Обсуждение: query help on Geography column

Поиск
Список
Период
Сортировка

query help on Geography column

От
Tony Sansom
Дата:
<p class="ox-79e96e5d2c-MsoNormal">HI I’m struggling with retuning data from the following query. It should be retuning
alldata within a 5k radius of a given Longitude, Latitude point.<p class="ox-79e96e5d2c-MsoNormal">Select stuff from
MyGeoTablewhere ST_DWithin(ST_GeographyFromText(‘SRID=4326;POINT(LONG,LAT)’), geog,5000)<p
class="ox-79e96e5d2c-MsoNormal">Isnot retuning all data it should be best guess there is an issue with the Geography
column this was created from a geometry column which is a collection of points lines and polygons<p
class="ox-79e96e5d2c-MsoNormal">Itried to recreate the Geography thus<p class="ox-79e96e5d2c-MsoNormal"
style="margin-left:72.0pt;">Update MyGeoTable set geog = ­(ST_Transform(geom,4326));<p
class="ox-79e96e5d2c-MsoNormal">Thisgives “ERROR: Geometry type (LINSTRING) dose not match column type (Polygon)”<p
class="ox-79e96e5d2c-MsoNormal">Myquestion  is twofold<p class="ox-79e96e5d2c-MsoNormal">1<sup style="line-height:
0;">st</sup>how can recreate the geography column, check it has worked?<p class="ox-79e96e5d2c-MsoNormal">2<sup
style="line-height:0;">nd</sup>  How might perform a similar query just using the geometry the 5k radius doesn’t have
tobe exact!<p class="io-ox-signature">Thanx for your help<br />

Re: query help on Geography column

От
"David G. Johnston"
Дата:
On Wed, Feb 17, 2016 at 3:28 AM, Tony Sansom <antonys@nsom.org.uk> wrote:

HI I’m struggling with retuning data from the following query. It should be retuning all data within a 5k radius of a given Longitude, Latitude point.

Select stuff from MyGeoTable where ST_DWithin(ST_GeographyFromText(‘SRID=4326;POINT(LONG,LAT)’), geog,5000)

Is not retuning all data it should be best guess there is an issue with the Geography column  this was created from a geometry column which is a collection of points lines and polygons

I tried to recreate the Geography thus

Update MyGeoTable set geog = ­(ST_Transform(geom,4326));

This gives “ERROR: Geometry type (LINSTRING) dose not match column type (Polygon)”

My question  is twofold

1st how can recreate the geography column, check it has worked?

2nd  How might perform a similar query just using the geometry the 5k radius doesn’t have to be exact!

​


​This seems like a poor choice of forum for these questions.  Of the official PostgreSQL mailing lists -general would be better.  But as these types and functions are not part of the core PostgreSQL product these are a bit off-topic for any of these lists.  I presume you are using PostGIS and so you should locate a list dedicate to discussing their product.

​-admin is mostly about DBA-related stuff like running and configuring a PostgreSQL instance.  Schema stuff could qualify (first question) but usually usage questions (2nd question) end up in either -general or -sql if they are agnostic enough.​

David J.