Is it possible to use spatial function to retrieve non-spatial data?

Question

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?

Answer 1

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)