- a table
crasheswith data on car crashes in nyc
- a table
locationswith 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.