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);
I would 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?
I’m on windows 7 using pgAdmin PostGIS 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
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)