0

I have written a query in PostGIS in which I use the ST_DWithin function to get the geometries which are located in a certain distance:

SELECT images.path, focnetworkpart.__gid 
FROM images INNER JOIN focnetworkpart 
ON ST_DWithin(images.geom, focnetworkpart.geom, 30000)
WHERE path='x.jpg';

As shown in the query above I use two different tables (images: with point geometry and focneworkpart with line geometry).

There is one more table which has point geometry (nodes table) and I want to include it in the above query. To be more clear, lets say that I have one input in the image table (with point geometry). What I want is to find all the geometries of node table and focnetworkpart table which are in distance 30000 from the point of the image table.

My question is if I can do this using only one query? Or they have to be two different.

EDITED This is the table called images (with point geometry)

enter image description here

This is the table called nodes (also with point geometry)

enter image description here

And this is the table called network (with polyline geometries)

enter image description here

What I want is to find all the line and nodes geometries which are within a certain distance from a point in images table. The output result of the query would be a table with four columns (imageId, PathId, nodeGeometry, PolylineGeometry).

  • You can do it in one query, but I am not sure how you are going to match up fotnetworkpart and nodes, based on a distance of 30000 from some image, as presumably there are different numbers of each? – John Powell Jan 19 '15 at 15:39
  • 1
    There are a few ways, and it is not clear what you would like the result to be. If possible, create a few sample output rows so we can back into it. – Scro Jan 19 '15 at 18:42
  • The biggest problem is the relation of the result set from the table node to the previously selected result set that is why I recommend the two query solution. – Benjamin Jan 19 '15 at 20:05
  • Scro I have edited the question adding more information regarding the output I want! – user1919 Jan 20 '15 at 8:16
1

I recommend two Queries, since the results from focnetworkpart and node are not related in anyway (at least according to your desciption).

The Problem you will or already ran into is the combination of both results. So you are better of first asking one table for results and then the other. Then you can work with both results.

Also be aware that ST_DWithin uses the units of the given SRID for the geometries. I GUESS that the 30000 are a meters, but if you use SRID 4326 for example distances are in degree!

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.