-1

Suppose I have a table containing some Locations belonging to different categories ('schools', 'hospitals', 'stations' etc...). I also have some points of interest (they can be in the same table if it's better or they can be in a different one, also asking advice on this) which can be something like 'bus stop', 'restaurant', 'pub' etc...

My question is: is it possible, with PostGIS (I'm going to use GeoDjango + PostGIS, but you can ignore the Django specific part if you want), to perform queries like these:

  1. Find all 'schools' within 1 Km from a 'station'
  2. Find all 'restaurants' within 2 Km of a 'park'
  3. Find all 'hospitals' within 2 Km of a 'park'
  4. etc...

I've seen example where, given a specific point or polygon, I could get all the records within a certain distance, but in this case there is not specific starting point. I need all the records of a certain category, within x Km from another category.

closed as unclear what you're asking by Vince, user30184, John Powell, underdark Nov 20 '16 at 20:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Welcome to GIS SE. As a new user please take the Tour. In it, you'll see that questions are expected to show some basic research effort, and coding questions are expected to contain code. – Vince Nov 20 '16 at 14:05
  • 1
    Hi @Vince thanks for your comment, but I would like to underline that I'm completely new both to PostGIS and to GIS in general. I'm in a stage where I don't even know if PostGIS is the solution for my problem and I would have expected a less harsh welcome, but still... – Andrea Grandi Nov 20 '16 at 14:50
  • 1
    Its not really a harsh comment as your question is in the first place a yes or no question. Is it possible with postgis? - the answer would be yes. But that would not help you to much probably. There is also another problem behind your question. The solution will run down to using buffers. But are you going to use a network based approach or just euclidean buffers? – Matte Nov 20 '16 at 16:22
  • @Matte thanks for answering if at least it's possible or not. About buffers, network based etc... I'm not sure what you are talking about. How are buffers related to this question? – Andrea Grandi Nov 20 '16 at 16:27
  • 1
    When you use locations and a distance you end up with a circular buffer around your locations. Thats what the answer of tinlyx suggest. But often you want to have distances along the road or a foodpath (as you can´t cross a river or walk straigth through a building). Therefore you need a network where the distances are measured along passable ways. Thats more complex, but for questions of reachability mostly the chosen option. – Matte Nov 20 '16 at 17:48
3

You can build a spatial join using relations such as ST_DWithin to join e.g. all hospitals within 3km of a school to the schools table.

Quoting the documentation linked above, you can use a query like:

SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.the_geom, h.hospital_name
    FROM schools s
        LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
    ORDER BY s.gid, ST_Distance(s.the_geom, h.the_geom);

, which will return find all hospitals to a/any school (not just one specific school).

Here the spatial join is similar to a regular join by attribute columns in relational algebra, but one can use a spatial relation (ST_DWithin) between two types of entities instead of e.g. the equality of common fields in the two tables.

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