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. Appreciate any ideas or guidance.

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

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