Return points present in circle of particular radius from specified location

Question

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

Answer 1

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.