I have a table of subway stops in NYC and what I am trying to do is select the points from the five closest subway stops to a given point. However, each subway 'stop' has multiple points associated with it due to there being many entrances for each stop.
The table looks like this:
|1||2-5||Birchall Ave & Sagamore St at NW corner||1734.0||http://web.mta.info/nyct/service/||POINT (-73.86835600032798|
|2||2-5||Birchall Ave & Sagamore St at NE corner||1735.0||http://web.mta.info/nyct/service/||POINT (-73.86821300022677|
|3||2-5||Morris Park Ave & 180th St at NW corner||1736.0||http://web.mta.info/nyct/service/||POINT (-73.87349900050798|
|4||2-5||Morris Park Ave & 180th St at NW corner||1737.0||http://web.mta.info/nyct/service/||POINT (-73.8728919997833|
|5||2-5||Boston Rd & 178th St at SW corner||1738.0||http://web.mta.info/nyct/service/||POINT (-73.87962300013866|
|6||2-5||Boston Rd & E Tremont Ave at NW corner||1739.0||http://web.mta.info/nyct/service/||POINT (-73.88000500027815|
If I split the 'name' field by "at" and select the first slice I can group all rows at the same 'stop' but how do I then return all rows associated with the 10 closest groups (stops)?
For instance, when I use this query:
select wkb_geometry from osm.subwaypoints s where s.line like '%C%' order by ST_Distance(ST_SetSRID( ST_Point( -73.96833020663865,40.68396650310555), 4326),s.wkb_geometry ) LIMIT 10
I get the nearest 10 entrances, but it only accounts for 2 actual stops