hammody (1) [Avatar] Offline
#1
Hi guys, I'll be grateful if any one could help me with this question.
Q / I've got two object-relational database to store the data for restaurant chains

1- Restaurants(restaurant_id, restaurant_name, food_type )

2- Branches (branches_id, branches_name, location)
Note:-
- branches_id REFERENCES Restaurants(restaurant_id).
- the type of location attribute is POINT
Now, I want to write SQL queries to retrieve the following information

A- The company and branch name of all branches which are within 2km of another branch of the same chain ?

B- All fastfood companies which have a branch within 2km of Hungry Jack’s, Queen Street Mall branch ?

My Regards
regina.leo (265) [Avatar] Offline
#2
Re: Query
Ahmed,

This kind of question is better answered on the PostGIS newsgroup and you'll probably get more than one good answer. This forum here is mostly focused on answering questions about examples in the book or questions/errata about book chapters.

we encourage you to join the PostGIS newsgroup
http://www.postgis.org/mailman/listinfo/postgis-users

For your below question, it really depends what spatial reference system you are using or if you are using the new Geography type that stores data in WGS 84 long lat but returns answers in meters.

If you are using Geography data type instead of geometry (which is introduced in PostGIS 1.5), then this question is much easier to answer because the answer using
ST_Distance and ST_DWithin will always be in meters for Geography type (and you can just use long lat data).

For your within query you would use

ST_DWithin

Check out the help on that -- it is quite good and the example I think demonstrates a similar use case that you are trying to do
http://www.postgis.org/documentation/manual-svn/ST_DWithin.html

Leo