Given a routable network table constructed by completing the guide at http://anitagraser.com/2011/02/07/a-beginners-guide-to-pgrouting/, how can I get an accurate list of all roads that are within a ring between 5 and 10 miles of a randomly selected start point?

The contents of the routing network table were derived from a shapefile of all roads in the NG 911 system available for download at my state's GIS data repository

Using Python and raw SQL, I'm doing the following:

query_select_ring = """
    SELECT * FROM road_network 
    WHERE st_distance_sphere(geom,st_pointfromtext('{0}', 4326)) < 10 * 1609.344 
    AND st_distance_sphere(geom, st_pointfromtext('{0}', 4326)) >= 5 * 1609.344;"""

The value use for st_pointfromtext is the result of the query

SELECT st_astext(st_startpoint)FROM road_network WHERE gid = {0};

where gid is the ID of a randomly selected row.

My issue is that on every ocassion the ring query has run, the values returned appear to have no rhyme or reason for their inclusion in the result set, with some points being within 2 miles of the start and others being 50-80 miles.

How can I refine the query to return an accurate result set of all roads that are within 5 miles inclusive and 10 miles exclusive of a randomly selected start point?

For testing data, I've uploaded the files to my google drive

https://drive.google.com/file/d/0B86VfDOuBm2IcWgwRXhCTGlkVXM/view?usp=sharing https://drive.google.com/file/d/0B86VfDOuBm2IZ29aRjFQS3RlZVE/view?usp=sharing

One is the dump file of 5000 rows (of approximately 140K) and the second is the schema ddl output resulting from

pg_dump --schema-only -t road_network geo > pgis_schema.ddl
  • What coordinate system are your points in? – raphael Feb 22 '16 at 6:03
  • Coordinate system is 4326 – Jason Feb 22 '16 at 11:15
  • Can you post some sample data for us to try troubleshooting? – raphael Feb 22 '16 at 16:00
  • Good suggestion, should have done it initially. The db is on my personal machine and I'm at work. Will post a data dump this evening. – Jason Feb 22 '16 at 16:06

Your Answer

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

Browse other questions tagged or ask your own question.