0

I have to make sure that a CHAR attribute of a class has the format of XX.XXXXX,YYY.YYYYY. X and Y can both be positive or negative and the maxim value of both has to be -90 to 90 for X and -180 to 180 for Y (altitude and latitude). But I dont know hot to use the CHECK at all. I would also like to know where can I find info about this topic of CHECK in CHAR,INT,DECIMAL, etc. Cant find what im looking for.

Thanks.

I tried:

CHAR(18) CHECK (coordenates = '^[-]?[0-9]{2}+(\.[0-9]{5}+)?,[-]?[0-9]{3}+(\.[0-9]{5}+)?$') and  2. CHAR(30) CHECK ( CAST (SUBSTRING_INDEX(geoposicio, ",", 1) AS DECIMAL(9,6)) BETWEEN -90 AND 90
  AND CAST(SUBSTRING_INDEX(geoposicio, ",", -1) AS DECIMAL(9,6)) BETWEEN -180 AND 180)

but it didn't work as you may imagine.

I expect that when I make an INSERT on the data base, it can only be a coordenate with the format I mentioned before. It doesn't have to be especifically like this (CHAR) but I'd rather not have to use other types as im new and I'm trying understand the basics of SQL.

Islam Elbanna
  • 1,438
  • 2
  • 9
  • 15
Math Enjoyer
  • 1
  • 2
    Is there a reason you aren't using MySQL's POINT data type? See https://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitude-in-a-mysql – Bill Karwin Jun 06 '23 at 22:22
  • 1
    Never say "didnt work", always say what _did_ happen – ysth Jun 06 '23 at 23:19
  • So 45,130 or 45.0,130.0 or 45.000000,130.000000 should all be allowed? – ysth Jun 06 '23 at 23:22
  • 1
    Don't use strings. Use POINT instead https://dev.mysql.com/doc/refman/8.0/en/gis-point-property-functions.html – Paul Maxwell Jun 07 '23 at 00:36

1 Answers1

1

I'd rather not have to use other types as I'm new and I'm trying understand the basics of SQL

One of the basics is to use the best/approriate "data type" for each need. You will make using lat/long data far harder (and less useful) by using strings - and it will probably not be useful learning. I strongly recommended using POINT column together with a spatial index e.g:

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  location POINT NOT NULL
);

CREATE SPATIAL INDEX sp_index ON mytable(location);

INSERT INTO mytable (id, location) 
   VALUES (1, ST_GeomFromText('POINT(40.71727401 -74.00898606)'));

To query the POINT column, the ST_X() and ST_Y() functions can be used to extract the latitude and longitude values, respectively

SELECT
      id
    , ST_X(location) AS latitude
    , ST_Y(location) AS longitude
FROM mytable;

To find info within a certain radius from a location, use the ST_Distance_Sphere() e.gg:

SELECT id, ST_X(location) AS latitude, ST_Y(location) AS longitude
FROM mytable
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(40.71727401 -74.00898606)')) <= 1000;

See Point Property Functions

Here is a MySQL reference for check constraints

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51