1

I have a table crashes with data on car crashes in New York City and a table locations with data on schools in New York City. I'm trying to return a table indicating whether each crash occurred within 500 feet of one or more schools that was open during the year of the crash, and if so which school(s). Both tables have the columns latitude and longitude. Here was my attempt:

SELECT
cr.collision_id,
cr.crash_date,
cr.crash_time,
cr.on_street_name AS crash_address,
cr.longitude AS crash_long,
cr.latitude AS crash_lat,
ST_MAKEPOINT(cr.longitude, cr.latitude) AS crash_point,
l.location_code AS school_code,
l.name1 AS school_name,
l.address AS school_address,
l.longitude AS school_long,
l.latitude AS school_lat,
ST_MAKEPOINT(l.longitude, l.latitude) AS school_point
FROM crashes AS cr
LEFT JOIN locations as l
ON ST_DWithin(ST_MAKEPOINT(l.longitude, l.latitude), ST_MAKEPOINT(cr.longitude, cr.latitude), 152.4)
WHERE cr.latitude IS NOT NULL;

This is returning a table with many, many rows per crash, each with schools listed, many of which appear to be nowhere near the crash. often the same school is appearing multiple times for each crash. So, this isn't working.

Does anyone know how to fix?

Do I need to specify an SRID or something?

Is ST_MAKEPOINT even what I want to be doing with the lat/longs?

I've looked through a number of similar questions (as well as this PostGIS tutorial) and haven't found a solution that works here.

Also, this query doesn't specify that the crash must have occurred in the same year that the school was open, which I haven't begun trying to figure out how to do. I'm assuming this involes a WHERE clause, but it's complicated by the fact that the years the schools were open are stored as fiscal years (7/1 - 6/30), whereas the crashes are stored as dates (e.g. '2021-11-30').

4

st_dwithin() uses the distance unit of the CRS. Since you are using lat-longs, the unit is in degree (*) and so the distance of 152.4 degrees means almost the entire world.

*:well, you haven't specified a CRS so it is technically unit-less, but since the coordinates values are expressed as degrees, the distance unit is still a degree.

What you can do is to cast the points to geography, which computes distances between lat-longs in meters

LEFT JOIN locations as l
ON ST_DWithin(ST_MAKEPOINT(l.longitude, l.latitude)::geography, ST_MAKEPOINT(cr.longitude, cr.latitude)::geography, 152.4)

For the date part, you can build the date range where clause, similar to

crash_date between (school_year||'/07/01')::date and (school_year+1||'/6/30')::date
4
  • Brilliant! This works. Thanks. If I can ask, just so I understand: (1) Why do I need to cast as geography? Aren't lat/long/the point they create already geographic coordinates? (2) Do I need to cast the ST_MAKEPOINT lines as geography in the SELECT statement as well? (3) Do I need to specify an SRID? Either with the ST_MAKEPOINT statements or ST_DWithin? Why/why not? Dec 8 '21 at 21:19
  • @AenidMansarde 1) geometry considers a flat world while geometry considers a round world. This has implication for distances computations (straight lines vs great circles), and one of the side effect is that the datatype geography natively computes distances between lat-longs in meters. 2) it's up to you and what you want to do with the geometry/geography! 3) I personally always specify an SRID, and constraint my columns to a specific data type, subtype, and CRS (like geometry(point, 4326)), as it prevents errors.
    – JGH
    Dec 9 '21 at 12:51
  • If you start to have a lot of data, you would greatly benefit from storing the geography points and to add a spatial index, it will greatly speed up the query
    – JGH
    Dec 9 '21 at 12:52
  • Thanks. If I cast the st_makepoints and the st_dwithin statements as geography, that will by default set the SRIDs to 4326, correct? I know the crash data I'm using uses 4326, but I'm not sure about the school locations data. If it turns out the school locations data uses a different SRID, will it return false results if I just cast it all as geography (and, as such, implicitly as 4326)? Do these concerns even matter when we're talking about a collection of individual points that are all within one city? Dec 9 '21 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.