1

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

UPDATE

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

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)

any suggestions?

  • You need to do some clarifying. 1) What is "decimal DMS"? See postgis.net/docs/manual-2.0/ST_AsLatLonText.html 2) Is the 5000 in ST_Transform() an SRID? See postgis.org/docs/ST_Transform.html – Martin F Dec 3 '13 at 18:28
  • please See Update for clarification thanx – Holly Dec 3 '13 at 21:54
  • The first line of your CREATE VIEW should end with keyword AS. – Scro Dec 3 '13 at 23:49
  • Your updates still make no sense. Are your inputs in DMS? PostGIS works in decimal degrees (when appropriate) and can convert them to DMS, but not the other way around (although people have publicized external functions). 1km = 1000m (0.5km = 500m). ST_Transform() takes two arguments, not three. – Martin F Dec 3 '13 at 23:57
  • There are several issues: 1) 'POINT(LAT LONG)' is the incorrect axis order; I highly recommend not formatting WKT and just directly use ST_MakePoint(long, lat) from the existing numeric data, 2) DMS != decimal degrees (22°30'00"W versus -22.5), 3) Why is it not practical to not use geography instead of geometry? 4) you cannot use ALTER TABLE on a VIEW, just use geom::geography AS geog to recast as geography in the view definition. – Mike T Dec 4 '13 at 2:10
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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