1

I have a following table and i would like to ask a question regarding coordinates check constraint.

create table places
(
    id          bigint generated always as identity
        constraint pk_places
            primary key,
    name        varchar(128) not null,
    address     varchar(256) not null,
    region_name varchar      not null
        constraint fk_places_region_name_regions
            references regions
            on update cascade on delete restrict,
    coordinates geography(Point, 4326)

I order to prevent wrong coordinates from being inserted in the table i have made following check constraint:

alter table places
add check (
    abs(ST_X(coordinates::geometry)) <= 90 and abs(ST_Y(coordinates::geometry)) <= 180
    );

It does work but as for me it seems quite fishy because:

  1. Well, geography type POINT, considering planet Earth should have restriction of lattitude and longitude of 90/180 degr respectively. But it does not. Perhaps it is any way how to switch it on somewhere maybe?
  2. In my check constraint i need to cast geography into geometry. Maybe there is any native function for geography to get lat and lon?
  3. Is it any better solution to restrict geo coordinates values?

Than you.

Aleksei Khatkevich
  • 1,923
  • 2
  • 10
  • 27
  • 1
    Investigate how the faulty coordinates are created. Some functions would prevent this, so maybe you can use them or validate the input before using the "unsafe" function (ex: `select st_geogFromText('point(200 200)');` -> `NOTICE: Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY`) ... on the other hand the output of such "safe" function is questionable (going across the pole, going back from the pole etc) – JGH Jun 28 '23 at 13:26

1 Answers1

1

to prevent wrong coordinates from being inserted

This is not possible. If I told you I'm in Chino, CA, that would be wrong. Because that is not where I am. But unless you already knew where I was (in which case, why am I telling you?), you would have no way of knowing it is wrong--it is a valid place at which someone could be. A check constraint could (possibly) prevent invalid coordinates, but can't prevent ones which are merely wrong.

It does work but as for me

It doesn't work for me. For one thing, you have your x and y reversed, so you disallow half the valid longitudes while you would be allowing invalid latitudes. For another thing, the point is normalized by the type input machinery before the check constraint gets its hands on it, so points which "lap the earth" will be converted to valid by the time they are checked.

jjanes
  • 37,812
  • 5
  • 27
  • 34