Promotion radius in miles around venues using PostGIS

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!

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,

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.

• 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 Aug 4 '11 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 Aug 4 '11 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 Aug 4 '11 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 Aug 4 '11 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 Aug 4 '11 at 20:27