# 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
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