Finding lat/longs within 500 feet of other lat/longs in PostGIS

Question

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').

Answer 1

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