5

Bear with me as I'm new to PostGIS and GIS in general. I also don't know the domain well enough to create a good title for this post! Here is what I'm trying to accomplish:

I have Venues in my system. Each Venue has a promotion radius in miles. Given an arbitrary point (lat/lng), I want to find all the Venues which encompass the arbitrary point in their promotion radius.

Here's what venues tables look like:

venues
id | name | latitude | longitude | promo_radius

Is this possible with PostGIS? Are there any resources that would help me further understand the query?

Thanks!

Improve this question

1 Answer 1

Reset to default
3

You don't give any information about your tables. Assuming the point isn't in a table and venues has fields id, radius, geom...

select
   venue.id
from
   venue
where
   st_dwithin(
            st_geographyfromtext( 'POINT(' || venues.longitude || ' ' || venues.latitude || ')'),
            'POINT(0 0)'::geography,
            venues.radius * 1609.344)

This goes through the venues table and

  • reads the point geometry stored for the venue in venues.geom,
  • and tests whether the POINT(0 0) is within venues.radius converted from miles to meters from the venue.

It returns the venue.id if they intersect.

There might be a more elegant way to construct the geography from the long/lat.

I recommend playing with SpatiaLite to learn about the basic geometry operations. It's easier to set up and get going than PostGIS. They have a nice tutorial, too. Most of the functions are the same if only named differently.

Improve this answer
5
  • Thanks Sean. I added the venues table to my question. It looks as though PostGIS doesn't want latitude/longitude values but wants a geometry point?
    – Mike
    Commented Aug 4, 2011 at 19:46
  • You can create the point on the fly if you want. But, if you're going to use PostGIS and do much analysis, you might as well store it as a geometry. Either transform it to a local coordinate system or store it as type 'geography'.
    – Sean
    Commented Aug 4, 2011 at 19:50
  • Thanks for the update Sean. Looks like st_dwithin in this case would be recognizing venues.geom as geometry and the venues.radius would be distance_of_srid. What is distance_of_srid? Is there a way to convert this to use geography instead of geometry? I'm working in miles, how do people usually handle the meters to miles conversion?
    – Mike
    Commented Aug 4, 2011 at 20:21
  • By the way, due to my production server provider, I'm only able to use PostGIS so SpatiaLite isn't really an option unfortunately.
    – Mike
    Commented Aug 4, 2011 at 20:23
  • I suggested SpatiaLite just to play with locally to familiarize yourself. Converting miles to meters is a simple as multiplying by 1609.344
    – Sean
    Commented Aug 4, 2011 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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