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.
    – til_b
    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
    – user3129056
    Dec 12, 2014 at 10:35
  • 2
    Please show the second (non-spatial) query. The question isn't very clear.
    – John Powell
    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
    – user3129056
    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.
    – Vince
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.