Re: [postgis-users] (no subject)

[Stephen Woodbridge]

Brent,

You solution is probably faster than mine and probably works even if the 
polygons are not valid.


Colin,

Sorry, best answer we could give without a full description of the problem.

So how does this case work?

*---* **
|   A   | | D  |
*---* **
**   *-*
| B  |   |C|
**   *-*
  *-*
  |E|
  *-*

So is A a neightbor of E? ie: narrow gap bewteen B and C
Is D a neighbor of E?

If these ar true, then you need to look into ray-tracing techniques to 
solve the problem, and there are no canned solution in postGIS that I 
can think of. If you search the archives I think there was a discussion 
of viewsheds 6+ months ago that might have yielded some stored 
procedures that you could try.


The problem here is that you need to evaluate every point along the 
boundary of say E and run a fan of rays outbound from each evaluation 
point and see what polygons they hit. Obviously there are an infinite 
number of evaluation points and an infinite number of rays from each 
point, so you have to decide on some sample size for both these items 
based on your tolerance for compute time and missed neighbors.


-Steve W

On 2/16/2011 6:44 AM, Colin wrote:

Hi Steve / Brent

Thanks for replies

Steve: Yes I did look at buffer but I discounted it because a small
number of the polys are at distances well outside the mean and I didnt
see any easy way of including them.
Brents solution with st_distance and a tolerance factor might suffer
from the same problem

And in fact the definition of adjacent doesnt include distance - its
simply that no other polygon crosses some path between any 2

I also have the centroids calced and stored so in my spatial innocence i
devised a solution which almost works and uses ST_Crosses(ST_MakeLine
something like this:

select a.id, b.id, ST_Crosses(ST_MakeLine(a.centroid, b.centroid),
b.geom) as stcrosses
from a, b where a.id = nnn and a.id!=b.id order by stcrosses

I run this per id from program code. I then filter the return to give
adjacents

I'm not at my dev machine right now and I suspect thats not exacty
right(at all!) but what I have isnt affected by distance and almost
works - except occasionally for the very complex and where there are
larger gaps between polys.

so maybe a combination of these methods?

Or since the data wont change often, maybe I just plot them in qgis and
do it manually! ;-)

Colin


On 02/16/2011 05:12 AM, Stephen Woodbridge wrote:

Colin,

Did you look at buffer?

Not tested but something like this might work where b.id are the
adjacent id's to a.id with the distance of.

select a.id, b.id from mypolys a, mypolys b
where a.id != b.id
and buffer(a.geom,)&&  b.geom
and intersects(buffer(a.geom,), b.geom)

-Steve

On 2/15/2011 5:38 PM, Colin wrote:

Hi

I'm quite new to postgis and spatial databases.
competent with sql and db's

My problem: How to locate adjacent polygons.

I have around around 2k irregular polygons.
They've have been calculated as alpha / concave hulls from point sets.
They're saved into pg as multipolygons

id | description | geom

The polygons dont have any regularity with regard to location and
interaction
the majority are close to a neighbour, but not touching. Typically
within +/- 1 - 5% of polygon max dim
a small number slightly overlap 1 or more neighbours, usually to quite a
small extent
their irregular shape can include 'undercuts'


I need to identify the adjacent neighbours for each polygon

I looked at various methods that might allow me to do this but I cant
get a 100% solution

Can anyone suggest methods that might work

thanks


Colin




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] How obtain information about Geometry type of a PGShapeGeometry

[Michele M]

Hi to all,
I've a PGShapeGeometry object witch is created by the constructor 
PGShapeGeometry(String text).Well.. if I want to know the type of Geometry 
specified into the text (e.g. POLYGON(..)), what kind of method I should use?   
 ___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ERROR: mixed dimension geometries

[Pierre Racine]

ST_MakeValid does the job but it is available only with 2.0...

>-Original Message-
>From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
>boun...@postgis.refractions.net] On Behalf Of Pierre Racine
>Sent: 16 février 2011 16:22
>To: PostGIS Users Discussion
>Subject: Re: [postgis-users] ERROR: mixed dimension geometries
>
>Forgot to say:
>
>ST_IsValid returns TRUE on both geometries...
>
>>-Original Message-
>>From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
>>boun...@postgis.refractions.net] On Behalf Of Pierre Racine
>>Sent: 16 février 2011 16:19
>>To: PostGIS Users Discussion
>>Subject: [postgis-users] ERROR: mixed dimension geometries
>>
>>Hi,
>>
>>What is the meaning of:
>>
>>ERROR: lwcollection_construct: mixed dimension geometries: 0/2
>>SQL state: XX000
>>
>>I get this when I do ST_Intersection() on two tables. Both tables contains 
>>only one geometry.
>>ST_Dimensions give 2 for both.
>>
>>How can I fix it?
>>
>>I tried ST_Buffer(geom, 0) without success.
>>
>>Thanks,
>>
>>Pierre
>>
>>___
>>postgis-users mailing list
>>postgis-users@postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>___
>postgis-users mailing list
>postgis-users@postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ERROR: mixed dimension geometries

[Pierre Racine]

Forgot to say:

ST_IsValid returns TRUE on both geometries...

>-Original Message-
>From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
>boun...@postgis.refractions.net] On Behalf Of Pierre Racine
>Sent: 16 février 2011 16:19
>To: PostGIS Users Discussion
>Subject: [postgis-users] ERROR: mixed dimension geometries
>
>Hi,
>
>What is the meaning of:
>
>ERROR: lwcollection_construct: mixed dimension geometries: 0/2
>SQL state: XX000
>
>I get this when I do ST_Intersection() on two tables. Both tables contains 
>only one geometry.
>ST_Dimensions give 2 for both.
>
>How can I fix it?
>
>I tried ST_Buffer(geom, 0) without success.
>
>Thanks,
>
>Pierre
>
>___
>postgis-users mailing list
>postgis-users@postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] ERROR: mixed dimension geometries

[Pierre Racine]

Hi,

What is the meaning of:

ERROR: lwcollection_construct: mixed dimension geometries: 0/2
SQL state: XX000

I get this when I do ST_Intersection() on two tables. Both tables contains only 
one geometry. ST_Dimensions give 2 for both.

How can I fix it?

I tried ST_Buffer(geom, 0) without success.

Thanks,

Pierre

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[Andrea Peri]

I found a solution using the ST_Split
this is a bit more long to write query, but work pretty well :)

select 1, ST_AsText(
ST_Difference(
ST_CollectionExtract(
ST_Split(
ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
ST_ExteriorRing(ST_GeomFromText('POLYGON((12 2, 10 10, 20
21, 21 5, 12 2))'))
),
2
),
ST_Intersection(
ST_CollectionExtract(
ST_Split(
ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
ST_ExteriorRing(ST_GeomFromText('POLYGON((12 2, 10 10,
20 21, 21 5, 12 2))'))
),
2
),
ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')
)
)
)



2011/2/16 strk 

> On Wed, Feb 16, 2011 at 07:08:03PM +0100, strk wrote:
> > On Wed, Feb 16, 2011 at 06:47:53PM +0100, Andrea Peri wrote:
> > > Look this simple example
> > >
> > > the difference between the line and the same line intersected with the
> > > polygon is equal to the line :)
> > >
> > > select 1, ST_Difference(ST_GeomFromText('LINESTRING(10.9 2, 11
> > > 8)'),ST_Intersection(ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
> > > ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')))
> > >
> > > the trick is that in the intersection point between polygon and line,
> the
> > > vertex added move the line so the difference fail to
> > > remove the internal portion.
> >
> > Ok, I think I know what's going on.
> > The original linestring isn't noded with the polygon boundary.
> > When computing the intersection, a node is added.
> > Such a node will NOT fall on the original line anymore, due
> > to precision constraints.
> >
> > See this:
> >  CREATE TABLE a AS SELECT
> >'LINESTRING(10.9 2, 11 8)'::geometry as g;
> >  CREATE TABLE b AS SELECT
> >'POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))'::geometry as g;
> >  CREATE TABLE c AS SELECT
> >ST_Intersection(a.g, b.g) FROM a, b;
> >  SELECT ST_Covers(a.g, ST_StartPoint(c.g)) FROM a,c; -- false
> >  SELECT ST_Covers(a.g,   ST_EndPoint(c.g)) FROM a,c; -- true
> >
> > What you could do is _snap_ the original line to the intersection,
> > or to node the input before proceeding.
>
> For a working example:
> select st_covers(a.g, st_startpoint(st_snap(a.g,c.g,1e-10))) from a,c;
>
> --strk;
>
>  ()   Free GIS & Flash consultant/developer
>  /\   http://strk.keybit.net/services.html
>



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[strk]

On Wed, Feb 16, 2011 at 07:08:03PM +0100, strk wrote:
> On Wed, Feb 16, 2011 at 06:47:53PM +0100, Andrea Peri wrote:
> > Look this simple example
> > 
> > the difference between the line and the same line intersected with the
> > polygon is equal to the line :)
> > 
> > select 1, ST_Difference(ST_GeomFromText('LINESTRING(10.9 2, 11
> > 8)'),ST_Intersection(ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
> > ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')))
> > 
> > the trick is that in the intersection point between polygon and line, the
> > vertex added move the line so the difference fail to
> > remove the internal portion.
> 
> Ok, I think I know what's going on.
> The original linestring isn't noded with the polygon boundary.
> When computing the intersection, a node is added.
> Such a node will NOT fall on the original line anymore, due
> to precision constraints.
> 
> See this:
>  CREATE TABLE a AS SELECT
>'LINESTRING(10.9 2, 11 8)'::geometry as g;
>  CREATE TABLE b AS SELECT
>'POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))'::geometry as g;
>  CREATE TABLE c AS SELECT
>ST_Intersection(a.g, b.g) FROM a, b;
>  SELECT ST_Covers(a.g, ST_StartPoint(c.g)) FROM a,c; -- false
>  SELECT ST_Covers(a.g,   ST_EndPoint(c.g)) FROM a,c; -- true
> 
> What you could do is _snap_ the original line to the intersection,
> or to node the input before proceeding.

For a working example:
select st_covers(a.g, st_startpoint(st_snap(a.g,c.g,1e-10))) from a,c;

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] How to locate adjacent polygons?

[pcreso]

Hi Colin,

Given your definition of adjacent, you are probably better off with a script 
using sqls to retrieve data/write result & the script to implement the logic, 
iterating through the polygons:
  
foreach polygon (a)
   foreach different polygon (b)
  foreach a.vertex
     foreach b.vertex
       get count of polygons intersected by a line between a.vertex and 
b.vertex
   if count > 2 then (a) and (b) are not adjacent
   next b.vertex
    next (a) vertex
    write result  
  next (b) polygon
next (a) polygon

This would define adjacency as having no intervening polygons between any two 
polygons, partial adjacency (where parts of two polygons are & other parts are 
separated) would require a slight change in the logic, so if any a.vertex & 
b.vertex have no intervening polygons, then (a) and (b) are adjacent. How you 
define & implement adjacency is up to you. As is the choice of scripting 
language :-)


HTH,

   Brent Wood



--- On Thu, 2/17/11, Colin  wrote:

From: Colin 
Subject: Re: [postgis-users] How to locate adjacent polygons?
To: postgis-users@postgis.refractions.net
Date: Thursday, February 17, 2011, 2:09 AM

sorry for double post - working from dodgy pda

Hi Steve / Brent

Thanks for replies

Steve: Yes I did look at buffer but I discounted it because a small
number of the polys are at distances well outside the mean and I didnt
see any easy way of including them.
Brents solution with st_distance and a tolerance factor might suffer
from the same problem

And in fact the definition of adjacent doesnt include distance - its
simply that no other polygon crosses some path between any 2

I also have the centroids calced and stored so in my spatial innocence i
devised a solution which almost works and uses ST_Crosses(ST_MakeLine
something like this:

select a.id, b.id, ST_Crosses(ST_MakeLine(a.centroid, b.centroid),
b.geom) as stcrosses 
from a, b where a.id = nnn and a.id!=b.id order by stcrosses

I run this per id from program code. I then filter the return to give
adjacents

I'm not at my dev machine right now and I suspect thats not exacty
right(at all!) but what I have isnt affected by distance and almost
works - except occasionally for the very complex and where there are
larger gaps between polys.

so maybe a combination of these methods?

Or since the data wont change often, maybe I just plot them in qgis and
do it manually! ;-)

Colin


On 02/16/2011 05:12 AM, Stephen Woodbridge wrote:
> Colin,
> 
> Did you look at buffer?
> 
> Not tested but something like this might work where b.id are the 
> adjacent id's to a.id with the distance of .
> 
> select a.id, b.id from mypolys a, mypolys b
> where a.id != b.id
> and buffer(a.geom, ) && b.geom
> and intersects(buffer(a.geom, ), b.geom)
> 
> -Steve
> 
> On 2/15/2011 5:38 PM, Colin wrote:
>> Hi
>>
>> I'm quite new to postgis and spatial databases.
>> competent with sql and db's
>>
>> My problem: How to locate adjacent polygons.
>>
>> I have around around 2k irregular polygons.
>> They've have been calculated as alpha / concave hulls from point sets.
>> They're saved into pg as multipolygons
>>
>> id | description | geom
>>
>> The polygons dont have any regularity with regard to location and
>> interaction
>> the majority are close to a neighbour, but not touching. Typically
>> within +/- 1 - 5% of polygon max dim
>> a small number slightly overlap 1 or more neighbours, usually to quite a
>> small extent
>> their irregular shape can include 'undercuts'
>>
>>
>> I need to identify the adjacent neighbours for each polygon
>>
>> I looked at various methods that might allow me to do this but I cant
>> get a 100% solution
>>
>> Can anyone suggest methods that might work
>>
>> thanks
>>
>>
>> Colin


-- 
http://www.fastmail.fm - IMAP accessible web-mail

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[strk]

On Wed, Feb 16, 2011 at 06:47:53PM +0100, Andrea Peri wrote:
> Look this simple example
> 
> the difference between the line and the same line intersected with the
> polygon is equal to the line :)
> 
> select 1, ST_Difference(ST_GeomFromText('LINESTRING(10.9 2, 11
> 8)'),ST_Intersection(ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
> ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')))
> 
> the trick is that in the intersection point between polygon and line, the
> vertex added move the line so the difference fail to
> remove the internal portion.

Ok, I think I know what's going on.
The original linestring isn't noded with the polygon boundary.
When computing the intersection, a node is added.
Such a node will NOT fall on the original line anymore, due
to precision constraints.

See this:
 CREATE TABLE a AS SELECT
   'LINESTRING(10.9 2, 11 8)'::geometry as g;
 CREATE TABLE b AS SELECT
   'POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))'::geometry as g;
 CREATE TABLE c AS SELECT
   ST_Intersection(a.g, b.g) FROM a, b;
 SELECT ST_Covers(a.g, ST_StartPoint(c.g)) FROM a,c; -- false
 SELECT ST_Covers(a.g,   ST_EndPoint(c.g)) FROM a,c; -- true

What you could do is _snap_ the original line to the intersection,
or to node the input before proceeding.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[Andrea Peri]

>>I try with the ST_Difference and the ST_Intersections but both seem to
>>work only with geometries of same type.


>ST_Difference should work.
>Do you have a _small_ example showing it doesn't ?

oops sorry,
I need to explain better

The ST_Difference effectively work with two geometry of different type, but
not always retrive the external portion,
the example I post show an example of because this happened.


2011/2/16 strk 

> On Tue, Feb 15, 2011 at 11:16:44PM +0100, aperi2007 wrote:
> > Hi,
> >
> > I need to retrieve the portion of a line external to a polygon,
> > I try with the ST_Difference and the ST_Intersections but both seem to
> > work only with geometries of same type.
>
> ST_Difference should work.
> Do you have a _small_ example showing it doesn't ?
>
> --strk;
>
>  ()   Free GIS & Flash consultant/developer
>  /\   http://strk.keybit.net/services.html
>



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[Andrea Peri]

Look this simple example

the difference between the line and the same line intersected with the
polygon is equal to the line :)

select 1, ST_Difference(ST_GeomFromText('LINESTRING(10.9 2, 11
8)'),ST_Intersection(ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')))

the trick is that in the intersection point between polygon and line, the
vertex added move the line so the difference fail to
remove the internal portion.


2011/2/16 strk 

> On Tue, Feb 15, 2011 at 11:16:44PM +0100, aperi2007 wrote:
> > Hi,
> >
> > I need to retrieve the portion of a line external to a polygon,
> > I try with the ST_Difference and the ST_Intersections but both seem to
> > work only with geometries of same type.
>
> ST_Difference should work.
> Do you have a _small_ example showing it doesn't ?
>
> --strk;
>
>  ()   Free GIS & Flash consultant/developer
>  /\   http://strk.keybit.net/services.html
>



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] shp2pgsql

[Mark Cave-Ayland]

On 16/02/11 14:29, Mehmet Erkek wrote:


Hi everyone,

Wishing you all a fruitful day, here is a problem we have and we are
hoping to get some help:

We are converting a shapefile to postgis with shp2pgsql. Conversion is
done without problem. However, it produces boundaries with fewer points
than we expect, thus it produces sharper boundaries and sometimes
overlaps with neighboring boundaries.

Is there way to make it produce outputs with more points and much closer
to original shapefile?

I appreciate any comment on this.

Thanks.


I think you need to clarify this with an example, since shp2pgsql 
doesn't alter the input geometries, it merely converts them to binary 
for storage within the database.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Storage of GML as BLOBs

[Finn Hedefalk]

Dear all,

When using the new version of Deegree WFS (http://www.deegree.org/), a 
popular storage method seems to be to store GML files as BLOBs in the 
PostgreSQL/PostGIS XML data type. One reason is to avoid the complex 
(and time-consuming) mappings required from a complex database schema. 
BLOBs seems to be an easy method if the task is to quickly provide data 
that confirms to certain specifications, such as the ones from the 
INSPIRE directive. That is, the purpose of the database is to be an 
intermediate storage when providing  certain data (however, in your 
source database, data are not stored as BLOBs).


I believe this approach may enhance the performance when you only need 
to provide some few GML datasets. However, I assume that when you have 
large amounts of data, there may be, for example, (1) performance 
impacts when doing spatial queries; (2) harder to create spatial 
indexes; and (3) storage problems because GML BLOBs takes up more space.


Are these assumptions correct?

Kind regards
Fin

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] shp2pgsql

[ibrahim saricicek]

Hi;

I prefer try also on ogr2ogr.
You can find it in FwTools, you have to download it from;
http://fwtools.maptools.org/
And can get help from;
http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

On Wed, Feb 16, 2011 at 4:29 PM, Mehmet Erkek  wrote:

> Hi everyone,
>
>
>
> Wishing you all a fruitful day,  here is a problem we have and we are
> hoping to get some help:
>
>
>
>
>
> We are converting a shapefile to postgis with shp2pgsql. Conversion is done
> without problem. However, it produces boundaries with fewer points than we
> expect, thus it produces sharper boundaries and sometimes overlaps with
> neighboring boundaries.
>
>
>
> Is there way to make  it produce outputs with more points and much closer
> to original shapefile?
>
>
>
>
>
> I appreciate any comment on this.
>
>
>
> Thanks.
>
>
>
>
>
> *Mehmet Erkek*
>
> www.REIDIN.com 
>
>
>
>
>
> This message is for the designated recipient only and may contain
> privileged, proprietary, or otherwise private information. If you have
> received it in error, please notify the sender immediately and delete the
> original. Any other use of the email by you is prohibited. Please Consider
> the Environment Before Printing This Email
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] dwithin or && BBox

[ibrahim saricicek]

Hi List;

I have four parameters lat, lon, idlist and bufferlist. In a function I can
find if point (lat lon) is in distance within the defined (in idlist)
objects

my_function(lon double precision, lat double precision, idlist character
varying, bufferlist character varying)
...

*The query is;*

query := 'select id ' ||
'from companyvectors inner join (Select
regexp_split_to_table('''||idlist||''', E''[,]+'') as a,
regexp_split_to_table('''||bufferlist||''', E''[,]+'') as b) as x ' ||
'on companyvectors.id=cast(x.a as bigint) ' ||
'where '||
'st_dwithin(st_transform(companyvectors.polygon_geom,900913),
st_transform(GeomFromText(''POINT('||lon||' '||lat||')'', 4326),900913),
cast(x.b as double precision));';

*What I need (it's faster) is a query something like (buffer is here
cast(x.b as double precision)**);*

polygon_geom && SetSRID('BOX2D(lat-buffer lon-buffer, lat+buffer
lon+buffer)', 4326)

Need help.. Thanks in Advance...
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] shp2pgsql

[Mehmet Erkek]

Hi everyone,

 

Wishing you all a fruitful day,  here is a problem we have and we are
hoping to get some help:

 

 

We are converting a shapefile to postgis with shp2pgsql. Conversion is
done without problem. However, it produces boundaries with fewer points
than we expect, thus it produces sharper boundaries and sometimes
overlaps with neighboring boundaries.

  

Is there way to make  it produce outputs with more points and much
closer to original shapefile?

 

 

I appreciate any comment on this.  

 

Thanks.

 

 

Mehmet Erkek

www.REIDIN.com   

 

 

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] How to locate adjacent polygons?

[Nicklas Avén]

Hallo Colin

Is it you that has this question at gis.stackexchange.com?
http://gis.stackexchange.com/questions/6098/how-do-you-produce-a-output-table-containing-distances-between-mineral-grains-at

Or at least I saw some similarity in the questions. 

>From what you write here, if I understand you right?

If you want to test polygonA, you want every polygon that gets some
light from polygonA if it is PlygonA is shining. 

That might be quite complicated. 

/Nicklas





On Wed, 2011-02-16 at 13:09 +, Colin wrote:
> sorry for double post - working from dodgy pda
> 
> Hi Steve / Brent
> 
> Thanks for replies
> 
> Steve: Yes I did look at buffer but I discounted it because a small
> number of the polys are at distances well outside the mean and I didnt
> see any easy way of including them.
> Brents solution with st_distance and a tolerance factor might suffer
> from the same problem
> 
> And in fact the definition of adjacent doesnt include distance - its
> simply that no other polygon crosses some path between any 2
> 
> I also have the centroids calced and stored so in my spatial innocence i
> devised a solution which almost works and uses ST_Crosses(ST_MakeLine
> something like this:
> 
> select a.id, b.id, ST_Crosses(ST_MakeLine(a.centroid, b.centroid),
> b.geom) as stcrosses 
> from a, b where a.id = nnn and a.id!=b.id order by stcrosses
> 
> I run this per id from program code. I then filter the return to give
> adjacents
> 
> I'm not at my dev machine right now and I suspect thats not exacty
> right(at all!) but what I have isnt affected by distance and almost
> works - except occasionally for the very complex and where there are
> larger gaps between polys.
> 
> so maybe a combination of these methods?
> 
> Or since the data wont change often, maybe I just plot them in qgis and
> do it manually! ;-)
> 
> Colin
> 
> 
> On 02/16/2011 05:12 AM, Stephen Woodbridge wrote:
> > Colin,
> > 
> > Did you look at buffer?
> > 
> > Not tested but something like this might work where b.id are the 
> > adjacent id's to a.id with the distance of .
> > 
> > select a.id, b.id from mypolys a, mypolys b
> > where a.id != b.id
> > and buffer(a.geom, ) && b.geom
> > and intersects(buffer(a.geom, ), b.geom)
> > 
> > -Steve
> > 
> > On 2/15/2011 5:38 PM, Colin wrote:
> >> Hi
> >>
> >> I'm quite new to postgis and spatial databases.
> >> competent with sql and db's
> >>
> >> My problem: How to locate adjacent polygons.
> >>
> >> I have around around 2k irregular polygons.
> >> They've have been calculated as alpha / concave hulls from point sets.
> >> They're saved into pg as multipolygons
> >>
> >> id | description | geom
> >>
> >> The polygons dont have any regularity with regard to location and
> >> interaction
> >> the majority are close to a neighbour, but not touching. Typically
> >> within +/- 1 - 5% of polygon max dim
> >> a small number slightly overlap 1 or more neighbours, usually to quite a
> >> small extent
> >> their irregular shape can include 'undercuts'
> >>
> >>
> >> I need to identify the adjacent neighbours for each polygon
> >>
> >> I looked at various methods that might allow me to do this but I cant
> >> get a 100% solution
> >>
> >> Can anyone suggest methods that might work
> >>
> >> thanks
> >>
> >>
> >> Colin
> 
> 


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] How to locate adjacent polygons?

[Colin]

sorry for double post - working from dodgy pda

Hi Steve / Brent

Thanks for replies

Steve: Yes I did look at buffer but I discounted it because a small
number of the polys are at distances well outside the mean and I didnt
see any easy way of including them.
Brents solution with st_distance and a tolerance factor might suffer
from the same problem

And in fact the definition of adjacent doesnt include distance - its
simply that no other polygon crosses some path between any 2

I also have the centroids calced and stored so in my spatial innocence i
devised a solution which almost works and uses ST_Crosses(ST_MakeLine
something like this:

select a.id, b.id, ST_Crosses(ST_MakeLine(a.centroid, b.centroid),
b.geom) as stcrosses 
from a, b where a.id = nnn and a.id!=b.id order by stcrosses

I run this per id from program code. I then filter the return to give
adjacents

I'm not at my dev machine right now and I suspect thats not exacty
right(at all!) but what I have isnt affected by distance and almost
works - except occasionally for the very complex and where there are
larger gaps between polys.

so maybe a combination of these methods?

Or since the data wont change often, maybe I just plot them in qgis and
do it manually! ;-)

Colin


On 02/16/2011 05:12 AM, Stephen Woodbridge wrote:
> Colin,
> 
> Did you look at buffer?
> 
> Not tested but something like this might work where b.id are the 
> adjacent id's to a.id with the distance of .
> 
> select a.id, b.id from mypolys a, mypolys b
> where a.id != b.id
> and buffer(a.geom, ) && b.geom
> and intersects(buffer(a.geom, ), b.geom)
> 
> -Steve
> 
> On 2/15/2011 5:38 PM, Colin wrote:
>> Hi
>>
>> I'm quite new to postgis and spatial databases.
>> competent with sql and db's
>>
>> My problem: How to locate adjacent polygons.
>>
>> I have around around 2k irregular polygons.
>> They've have been calculated as alpha / concave hulls from point sets.
>> They're saved into pg as multipolygons
>>
>> id | description | geom
>>
>> The polygons dont have any regularity with regard to location and
>> interaction
>> the majority are close to a neighbour, but not touching. Typically
>> within +/- 1 - 5% of polygon max dim
>> a small number slightly overlap 1 or more neighbours, usually to quite a
>> small extent
>> their irregular shape can include 'undercuts'
>>
>>
>> I need to identify the adjacent neighbours for each polygon
>>
>> I looked at various methods that might allow me to do this but I cant
>> get a 100% solution
>>
>> Can anyone suggest methods that might work
>>
>> thanks
>>
>>
>> Colin


-- 
http://www.fastmail.fm - IMAP accessible web-mail

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[strk]

On Tue, Feb 15, 2011 at 11:16:44PM +0100, aperi2007 wrote:
> Hi,
> 
> I need to retrieve the portion of a line external to a polygon,
> I try with the ST_Difference and the ST_Intersections but both seem to 
> work only with geometries of same type.

ST_Difference should work.
Do you have a _small_ example showing it doesn't ?

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] (no subject)

[Colin]

Hi Steve / Brent

Thanks for replies

Steve: Yes I did look at buffer but I discounted it because a small
number of the polys are at distances well outside the mean and I didnt
see any easy way of including them.
Brents solution with st_distance and a tolerance factor might suffer
from the same problem

And in fact the definition of adjacent doesnt include distance - its
simply that no other polygon crosses some path between any 2

I also have the centroids calced and stored so in my spatial innocence i
devised a solution which almost works and uses ST_Crosses(ST_MakeLine
something like this:

select a.id, b.id, ST_Crosses(ST_MakeLine(a.centroid, b.centroid),
b.geom) as stcrosses 
from a, b where a.id = nnn and a.id!=b.id order by stcrosses

I run this per id from program code. I then filter the return to give
adjacents

I'm not at my dev machine right now and I suspect thats not exacty
right(at all!) but what I have isnt affected by distance and almost
works - except occasionally for the very complex and where there are
larger gaps between polys.

so maybe a combination of these methods?

Or since the data wont change often, maybe I just plot them in qgis and
do it manually! ;-)

Colin


On 02/16/2011 05:12 AM, Stephen Woodbridge wrote:
> Colin,
> 
> Did you look at buffer?
> 
> Not tested but something like this might work where b.id are the 
> adjacent id's to a.id with the distance of .
> 
> select a.id, b.id from mypolys a, mypolys b
> where a.id != b.id
> and buffer(a.geom, ) && b.geom
> and intersects(buffer(a.geom, ), b.geom)
> 
> -Steve
> 
> On 2/15/2011 5:38 PM, Colin wrote:
>> Hi
>>
>> I'm quite new to postgis and spatial databases.
>> competent with sql and db's
>>
>> My problem: How to locate adjacent polygons.
>>
>> I have around around 2k irregular polygons.
>> They've have been calculated as alpha / concave hulls from point sets.
>> They're saved into pg as multipolygons
>>
>> id | description | geom
>>
>> The polygons dont have any regularity with regard to location and
>> interaction
>> the majority are close to a neighbour, but not touching. Typically
>> within +/- 1 - 5% of polygon max dim
>> a small number slightly overlap 1 or more neighbours, usually to quite a
>> small extent
>> their irregular shape can include 'undercuts'
>>
>>
>> I need to identify the adjacent neighbours for each polygon
>>
>> I looked at various methods that might allow me to do this but I cant
>> get a 100% solution
>>
>> Can anyone suggest methods that might work
>>
>> thanks
>>
>>
>> Colin

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] syntax to pick correct, single result from subquery

[Ture Pålsson]

2011/2/16 karsten vennemann :

> [ ... ]
> I also have this so far
>
> update ca.r_model_cells set quad_id =
> (select quad.quad_id from ca.quad, ca.r_model_cells
> where
> quad.merc_geom && r_model_cells.merc_geom
> and intersects(quad.merc_geom,r_model_cells.merc_geom));

I think you can get rid of the subquery entirely by using the UPDATE
... SET ... FROM syntax. Something like

UPDATE r_model_cells
SET quad_id = quad.quad_id
FROM quad
WHERE intersects(quad.merc_geom, r_model_cells,merc_geom)
;


  -- T
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] syntax to pick correct, single result from subquery

[Birgit Laggner]

 Hi Karsten,

try (I hope I made no mistake...):

update ca.r_model_cells a set quad_id =b.quad_id from
(select quad.quad_id, r_model_cells.record_id rid from ca.r_model_cells 
inner join ca.quad

on quad.merc_geom && r_model_cells.merc_geom
where st_within(r_model_cells.merc_geom,quad.merc_geom)) b
where a.record_id=b.rid;

You have to relate the results of the subquery to some row identificator 
in the target table, otherwise it tries to write all results of the 
subquery into every row of the target table...


Regards,

Birgit.

On 16.02.2011 10:03, karsten vennemann wrote:

Ok I am fighting with this SQL task:
I have a polygon layer "quad" and a point location layer 
"r_model_cells" in PostGIS. In each of the quads there will be one or 
more of the pints located.
Now I would like to update a field 'quad_id' in he point layer 
"r_model_cells" with the id of the quad it is located within. Thus I 
can get all point ids and respective quad ids they are via this query
select quad.quad_id qid, r_model_cells.record_id rid from ca.quad, 
ca.r_model_cells

where st_within(r_model_cells.merc_geom,quad.merc_geom);
but how can I use this query to update a column my field quad_id in he 
point layer. I can'get my snytax together :(

I also have this so far
update ca.r_model_cells set quad_id =
(select quad.quad_id from ca.quad, ca.r_model_cells
where
quad.merc_geom && r_model_cells.merc_geom
and intersects(quad.merc_geom,r_model_cells.merc_geom));
But this query will rerieve more then one records in the subquery ...
Thus I get ERROR:
more than one row returned by a subquery used as an expression I want 
to limit to one which correcponds to the point in question

Any ideas what my syntax should be ?
Note: Right now I can do it via two queries below
-
select quad.quad_id qid, r_model_cells.record_id AS rid
into temp_rid
from ca.quad, ca.r_model_cells
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;
and then
update ca.r_model_cells set quad_id =
qid from (select temp_rid.qid qid, temp_rid.rid, 
r_model_cells.record_id from temp_rid, ca.r_model_cells

where temp_rid.rid =r_model_cells.record_id ) as bubu ;
-
Cheers
Karsten


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] syntax to pick correct, single result from subquery

[karsten vennemann]

Ok I am fighting with this SQL task:
 
I have a polygon layer "quad" and a point location layer "r_model_cells" in 
PostGIS. In each of the quads there will be one or more of the pints located. 
Now I would like to update a field 'quad_id' in he point layer "r_model_cells" 
with the id of the quad it is located within. Thus I can get all point ids and 
respective quad ids they are via this query
 
select quad.quad_id qid, r_model_cells.record_id rid from ca.quad, 
ca.r_model_cells 
where st_within(r_model_cells.merc_geom,quad.merc_geom);
 
but how can I use this query to update a column my field quad_id in he point 
layer. I can'get my snytax together :(
 
I also have this so far
 
update ca.r_model_cells set quad_id = 
(select quad.quad_id from ca.quad, ca.r_model_cells 
where
quad.merc_geom && r_model_cells.merc_geom 
and intersects(quad.merc_geom,r_model_cells.merc_geom));
 
But this query will rerieve more then one records in the subquery ...
Thus I get ERROR:  
more than one row returned by a subquery used as an expression I want to limit 
to one which correcponds to the point in question
Any ideas what my syntax should be ?
 
 
Note: Right now I can do it via two queries below 
-
select quad.quad_id qid, r_model_cells.record_id AS rid 
into temp_rid
from ca.quad, ca.r_model_cells 
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;
 
and then 
 
update ca.r_model_cells set quad_id = 
qid from (select temp_rid.qid qid, temp_rid.rid, r_model_cells.record_id from 
temp_rid, ca.r_model_cells 
where temp_rid.rid =r_model_cells.record_id ) as bubu ;
-
 
 
Cheers
Karsten
 
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users