I have a postgis table that is updated on a regular basis so it is not practical to cast my geometry column to geography
So can I modify the following query to convert a distance say 0.5k into decimal Degrees on the fly at the latitude of the given point?
Select colA,colB,lat_dms,long_dms FROM my_geo_table WHERE ST_DWithin(SRID=4326;POINT(LONG LAT)’,geom,0.5);
Id be replacing 0.5 which in above equals 30 minutes with ST_Transform(500) but am unsure of the syntax
Or would I be better of creating and saving a VIEW
CREATE VIEW geog_from_geom_view Select colA,colB,lat_dms,long_dms FROM my_geo_table; ALTER TABLE geog_from_geom_view ADD column geog; Update geog_from_geom_view set geog = ST__GeographyFromText(ST_as_Text(my_geo_table.geom));
I tried above but cauld not make it work I kept getting an error saying geog_from_geom_view is not a table
also tried ALTER VIEW with Same error
If I created a View what would the Syntax of the new query be?
Thanx in advanced BTW I’m on windows 7 using pgAdmin posgis 2.0 with no CMD line access
To answer the questions martin f 1)
Should be decimal Degrees
the 500 in ST_Transform() an SRID?
no it is 0.5k in meters so should it be ST_Transform(POINT, 500, 4326);
but that doesn't make sense when I'm trying to find all points within 0.5k of a given LAT Long
Update 2 in PgAdmin I created a view my view definition now looks like this
select colA,colB,lat_dms,long_dms ,geom FROM my_geo_table, Geog GEOGRAPHY(POINT,4326), geom::geography AS geog;
I now get an syntax error at or near “4326” LINE 4 Geog GEOGRAPHY(POINT,4326)