Avoiding this PostGIS error: "Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY"

Question

I have:

• a table crashes with data on car crashes in nyc
• a table locations with data on the locations of NYC schools

Both have latitude and longitude columns.

I'm trying to generate a table with one row for either: (a) every crash that occurred more than 400 feet from a school OR (b) every crash that occurred within 400 feet of a school (with additional rows for each crash if it occurred within 400 feet of more than one school)

Here's my query:

SELECT
cr.collision_id,
cr.crash_date,
cr.crash_time,
cr.borough,
l.location_code,
l.name1,
l.address,
cr.longitude,
cr.latitude,
ST_MAKEPOINT(cr.longitude, cr.latitude)::geography AS crash_point,
l.longitude,
l.latitude,
ST_MAKEPOINT(l.longitude, l.latitude)::geography AS school_point
FROM crashes AS cr
LEFT JOIN locations AS l
ON ST_DWithin(ST_MAKEPOINT(l.longitude, l.latitude)::geography,
              ST_MAKEPOINT(cr.longitude, cr.latitude)::geography, 121.92)
WHERE cr.crash_date BETWEEN '2017-07-01' AND '2019-06-30'
    AND ((cr.crash_date BETWEEN (l.year1-1||'/07/01')::date AND (l.year1||'/6/30')::date) OR l.year1 IS NULL)
    AND cr.latitude IS NOT NULL
    AND cr.latitude <> 0;

It's taking very long to run, and while it's running I'm getting the following error message over and over again:

Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY

When I query just the lat/longs/makepoints for crashes and, separately, for locations, I don't get that error and they return just fine. That suggests the problem is with the join, but I don't know what's wrong with it.

I've checked all the lat/longs for those crashes in excel and they're within the appropriate ranges (except for the ones I excluded in the WHERE clause). There's no lats in the long column or vice versa.

When I run this with other, smaller date ranges (e.g. 2021-06-30 to 2021-07-01), I don't get the error and it returns fine.

Any ideas why this may be happening?

Additionally, do I need to worry about specifying an SRID? I read here that if you don't specify an SRID, it defaults to 4326. So I'm assuming that's happening with my query, which I'm hoping is fine?

One issue here is I'm not sure what SRID the agency used with the locations lat/longs (I've asked). And 1/10 of the lat/longs in locations I had to find myself on google maps, which apparently uses a different SRID. Not sure if this is a problem. (I know that crashes uses 4326.)

I tried adding ST_SetSRID( ... , 4326) around the ST_MakePoints and ST_DWithin and got the same result.

Some of the lat/longs have different numbers of characters after the decimal points. Don't know if that matters.

Answer 1

To help you understand and avoid

Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY

Try

-- Both 1st queries return
-- NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
SELECT ST_MAKEPOINT(192.38, 18.34)::geography;
SELECT ST_MAKEPOINT(18.24, 192.38)::geography;
-- Third one does not
SELECT ST_MAKEPOINT(172.38, 18.34)::geography;

It means your longitude coordinates are wrong as x the longitude is lower than -180 or upper than 180 or the y, the latitude is upper than 90 or lower than -90.

It can be due to not using longitude, latitude or because your are switching longitude with latitude

To get an overview where the issue lies, do

SELECT min(longitude) as min_x, min(latitude) as min_y,
       max(longitude) as max_x, max(latitude) as max_y
FROM crashes;

and

SELECT min(longitude) as min_x, min(latitude) as min_y,
       max(longitude) as max_x, max(latitude) as max_y
FROM location;

Maybe related to the fact you picked some coordinates from Google Maps: if you have inserted lat, lon (y, x) in longitude, latitude (x, y) coordinates, do not expect it will work well...