2

I'm using PostGIS in postgresql.I wish to calculate points within the radius from particular geocode.For that i have created a table with spatial column geocode having datatype as geometry and inserted some values using following query.

INSERT INTO aspatial_address( addressline,country,geocode) VALUES ('','', ST_GeomFromText('POINT(14.083333 70.583333)', 4326));

As geocode having geometry datatype inserted values of geocode are stored in hex format.Ex. for "POINT(10,19.1)" it stores '010100000000000000000024409A99999999193340' as geocode.

I can retrieve these value in normal geocode format using ST_AsText(geocode)geocode function using : 

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

Till everything is fine.

Now i wish to select geocode values from non-spatial postgres tables. for that i have tried same query but their in incompatibility between datatype of geocode.As spatial have geometry datatype and non-spatial have character varying datatype it gives error.Can i convert datatype through query using any function or i have to change query?

Improve this question
5
  • I dont fully understand the question. You have a non-spatial database, where you store the WKB representation of a point in a column with type varchar? If thats the case, you can use the function ST_PointFromWKB postgis.refractions.net/documentation/manual-1.4/… on the WKB value to get a geometry instance. Commented Dec 12, 2014 at 10:25
  • thanks @til_b for reply.I have non-spatial db and i wish to use inbuilt function of postgis on that db.is it possible? In the Starting part of question i have tried query on sample spatial table but actually i wish to use it on non-spatial Commented Dec 12, 2014 at 10:35
  • 2
    Please show the second (non-spatial) query. The question isn't very clear. Commented Dec 12, 2014 at 11:29
  • @ John Barça thanks for reply.my question is - how can i use non-spatial query to retrieve points from circle within particular radius? i have updated my title.Please take a look Commented Dec 12, 2014 at 11:33
  • 2
    The purpose of spatial queries is to exploit the spatial index. While it is possible to store WKT or WKB in a table and use it in spatial query constraints, this will be very inefficient and unsuitable for all but trivial implementations. Commented Dec 12, 2014 at 12:50

1 Answer 1

Reset to default
2

Assuming your structure is someting like this

table A ( id int, location varchar(MAX) )

and the data

insert into A VALUES(1, '010100000000000000000024409A99999999193340')

then you can (bearing in mind the huge performance penalty mentioned by @Vince ) query your database using something like

select * from A where ST_Point_Inside_Circle(ST_PointFromWKB(A.location),14.083333,19.583333,7)

(all code untested)

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.