Hot answers tagged query
I think you're making it too complicated. Try this instead: SELECT sum(building_pop) FROM Buildings WHERE ST_Distance(ST_SetSRID( ST_MakePoint(1116858.6062789 7086290.6680572), 900913), geom) < 10000 That would give you the sum of building_pop column of all the buildings that are less than 10,000 units away from the defined Point.
You best start with the workshop, and especially look at the chapter, which explains how to write a simple plpgsql wrapper function to return additional attributes such as street name for example. A query might look like this then: SELECT seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.osm_name FROM pgr_dijkstra(' SELECT id, ...
You have a couple of problems within your query. The usage of CTEs you completely prevent using your geometry index, which I hope you have on gps_nodes.geog. The usage of ST_DISTANCE does not use your geometry index as well. Use something like ST_DWITHIN. By the way ST_DWITHIN with your CTE construct does not use the geometry index. (not performance) I ...
Add AND ST_DWithin("geog",ST_GeogFromText('POINT( -1.54790 53.79424 )'), 40000) to Select where distance is smaller than 40km , so it doesn not calculate distance to all points just for those it needs And you probably can use just one gps_nodes_1 "table", far as i can see it is same query as _2
Only top voted, non community-wiki answers of a minimum length are eligible