Transform distance or create view using PostGIS [closed]

Question

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)

decimal DMS

Should be decimal Degrees

2)

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)

any suggestions?

Answer 1

To make a view that casts one datatype to another, it's simple, just use ::newdatatype in the select part of the query:

CREATE VIEW geog_from_geom_view 
  SELECT gid, geom::geography AS geog, cola, colb
  FROM my_geo_table;

But with the original table my_geo_table, it doesn't make any specific performance difference whether the location is stored as a geometry or geography type. Pick one based on what you need. You can even make an insert/update trigger to cast the object from the numeric lat/long data.

To select data within 0.5 km (500 m) of a point, you can either cast a geography from the query, or use the view.

SELECT * FROM my_geo_table
WHERE ST_DWithin(geom::geography, ST_MakePoint(:LONG, :LAT), 500.0);

-- or

SELECT * FROM geog_from_geom_view
WHERE ST_DWithin(geog, ST_MakePoint(:LONG, :LAT), 500.0);

Neither of these will have great query performance, since they will need to cast the geometry for the whole table and do a sequence scan (slow). To improve it there are few options, such as using an index based on an expression, or store geography types instead of geometry types. Adding an index, however, would make frequent table updates slower.