1

I'm using PostgreSQL. I wish to return the points withing circle from particular point that is geocode. Now i have a geocode and radius of circle. From this data can i calculate the points present within the circle? Actually i have tried this with PostGIS and it's working fine but PostGIS deals with spatial data only and i have non-spatial data. In PostGIS i have used following query :

select addressid,ST_AsText(geocode)geocode from aspatial_address as A where ST_Point_Inside_Circle(a.geocode,14.083333,19.583333,7)

my table structure is :

create table maddress
{
 addressid bigint(30) primary key;
 addressline varchar2(40) not null;
 geocode varchar2(20) not null;
}

and i wish to return the points present in circle whose radius is 7 km from geocode(14.083333,19.583333).

Now, hint me..how can i do this in PostgreSQL? Is it possible here? Is their any geometric function used in PostgreSQL? Please let me know..

user3129056
  • 447
  • 3
  • 6
  • 19
  • PostGIS does not deal with spatial data only. It is an extension to PostgreSQL, so you do your spatial predicates with PostGIS functions and relate that output to any other relation you would ordinarily do in PostgreSQL. Can you show table structures and give an example of the data you want to see related in a single output? – Patrick Dec 15 '14 at 06:18
  • Hi @Patrick..Thanks for reply..i have updated my question with table structure and my query..Please take a look and let me know – user3129056 Dec 15 '14 at 06:26

1 Answers1

0

PostgreSQL has a few geometry types that you can use:

SELECT addressid, addressline, geocode
FROM maddress
WHERE geocode::point <@ circle '((14.083333,19.583333), 7)';

Note that if geocode produces x and y with non-standard punctuation, you should use string function to parse geocode into a format that the point type will accept.

Keep in mind that if geocode and (14.083333,19.583333) are in long-lat coordinates you cannot use the PostgreSQL geometry types because the units are different (spherical long-lat in degrees for the geocode locations and center of the circle vs planar distance in km for the radius). In that case you have to use PostGIS to transform the long-lat coordinates to some planar coordinate reference system.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • how to use string function to parse geocode? I'm taking inputs(lat,long and radius) dynamically. At the run time i'm passing values at that time it gives error . Why so? – user3129056 Dec 15 '14 at 09:54
  • Edit your question to show **exactly** what a geocode value looks like and I will edit my answer. – Patrick Dec 15 '14 at 10:41
  • I have posted a question regarding this.'ll you please take a look at http://stackoverflow.com/questions/27480846/error-the-column-index-is-out-of-range-1-number-of-columns-0 – user3129056 Dec 15 '14 at 10:46