[postgis-users] Point in Polygon Problem

[Marcos Paul]

Hi Folks,

I am trying to solve a simple problem: the point in polygon. First of all, I
have a CSV file with the polygons in list similar to the WKT Format, that is
something like:

 BUILT_UP_AREA_ID VERTEX_SEQ LATITUDE LONGITUDE  1081 0 152.6657 -25.5206
1081 1 152.6662 -25.5211  1081 2 152. -25.5216  1081 3 152.6673 -25.5224
1081 4 152.668 -25.523  1081 5 152.6694 -25.5247  1081 6 152.67 -25.5255
1081 7 152.6707 -25.5263  1081 8 152.6734 -25.5291  1081 9 152.6741 -25.5295
1081 10 152.6745 -25.5299  1081 11 152.675 -25.5301  1081 12 152.676
-25.5313  1081 13 152.6762 -25.5317  1081 14 152.6764 -25.5321  1081 15
152.6764 -25.5333  1081 16 152.6766 -25.534  1081 17 152.6766 -25.5341  1081
18 152.6771 -25.5341  1081 19 152.6774 -25.534  1081 20 152.6788 -25.5354
1081 21 152.6797 -25.5361  1081 22 152.6788 -25.5368


>From there I want to export that to my postgis data base, I solved that
converting the CSV to a Shape file and then applying the shp2psql gui.

When I am trying to check if a particular point is inside my table I am just
applying the following command:

select ST_ASTEXT(the_geom) as multipolygons FROM Table_Polygons WHERE
ST_Contains(Table_Polygons.the_geom, ST_GeometryFromText('POINT(X,Y)'));

So after that, I am getting that the point X,Y is not inside one of the
polygons of my file, when I am completely sure that this point is actually
inside a polygon.

What I am doing wrong?  Does the fact that I am using Multipolygons affect
at all?

Thank you for your help,

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

Re: [postgis-users] issue while building postgis-1.5.2 with pg-9

[Dharmendra Goyal]

Hi Mark,

I added one CFLAG /GS- and security cookie related warnings went away but
following symbols still could not be found:

c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0x6e7):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0x6fc):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0xade):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0xaf3):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridlist.obj):(.text+0x1a2):
undefined reference to `_imp___wassert'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_tmerc.obj):(.text+0x917):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_robin.obj):(.text+0x2e):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_robin.obj):(.text+0x181):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(nad_intr.obj):(.text+0x21):
undefined reference to `_ftol2_sse'
c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(nad_intr.obj):(.text+0x42):
undefined reference to `_ftol2_sse'
collect2: ld returned 1 exit status

This is coming at the time of linking:
dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll  --def
libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o
lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o
lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o
lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o
lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_export.o
lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o lwgeom_in_gml.o
lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o
long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o geography_inout.o geography_gist.o
geography_btree.o geography_estimate.o geography_measurement.o
 -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition
 -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib -L/C/pgBuild/zlib/lib
-Wl,--as-needed   -L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib
-L/C/pginstaller.pune-repo/proj-4.7.0.staging/lib -lgeos_c -lproj
-L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a
-Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres

Any clue...??

Regards,
Dharmendra

On Thu, Oct 21, 2010 at 4:33 PM, Mark Cave-Ayland <
mark.cave-ayl...@siriusit.co.uk> wrote:

> Dharmendra Goyal wrote:
>
>  Hi,
>>
>> I am trying to build postgis-1.5.2 with postgresql-9.0.1 and i am getting
>> following error while building the sources:
>>
>> c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x47):
>> undefined reference to `__security_cookie'
>> c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x539):
>> undefined reference to `...@__security_check_cookie@4'
>>
>> Error is coming just after executing:
>> make[1]: Entering directory
>> `/c/pginstaller.pune-repo/postgis.windows/postgis'
>> dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll  --def
>> libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o
>> lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o
>> lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o
>> lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o
>> lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_export.o
>> lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o lwgeom_in_gml.o
>> lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o
>> long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o geography_inout.o geography_gist.o
>> geography_btree.o geography_estimate.o geography_measurement.o
>>  -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition
>>  -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib -L/C/pgBuild/zlib/lib
>> -Wl,--as-needed   -L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib
>> -L/C/pginstaller.pune-repo/proj-4.6.1.staging/lib -lgeos_c -lproj
>> -L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a
>> -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres
>>
>
> Looks like an MSVC-specific issue with the PROJ.4 library to me:
> http://blogs.msdn.com/b/nikolad/archive/2005/01/27/unresolved-external-security-cookie-with-platform-sdk-compiler.aspx
>
>
> 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
>



-- 
Dharmendra Goy

Re: [postgis-users] Database restore fails with missing liblwgeom

[Lee Hughes]

The databases were created by restoring the pg_dumpall backup, so I  
installed Postgis (psql -d [yourdatabase] -f postgis.sql) in all of  
them and tried the restore again, with the same errors. In addition to  
the liblwgeom errors (below), which presumably I can ignore, the  
restore also errors with "type geometry does not exist". Can I ignore  
those errors too?


Thanks for the help.


Date: Tue, 19 Oct 2010 20:17:25 -0400
From: "Paragon Corporation" 
Subject: Re: [postgis-users] Database restore fails with missing
liblwgeom
To: "'PostGIS Users Discussion'"

Message-ID: <61923d39edd5403cb5f5c31abab20...@j>
Content-Type: text/plain;   charset="us-ascii"

Lee,

The liblwgeom.so was renamed in postgis 1.4 to postgis-1.4... So,
postgis-1.5... So

The easiest way to restore your data is to

1) Create a new database
2) install PostGIS 1.5  (the install files should bie in
share/contrib/postgis-1.5
3) Then restore your data on top
4) You'll should then get errors that the functions already exist and they
will be skipped.  You may get some errors too about the liblwgeom, but those
you can ignore (unless you are using them)
 since they represent functions long ago deprecated that got taken out of
postgis-1.5

Leo
http://www.postgis.us



-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Lee
Hughes
Sent: Tuesday, October 19, 2010 7:15 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] Database restore fails with missing liblwgeom

Restoring a pg_dumpall backup from a Postgres 8.1.3 + Postgis 1.1.4 server
on a Postgres 9 + Postgis 1.5.2 server fails with

could not access file "$libdir/liblwgeom.so.1.1": No such file or directory

Postgis compiled/installed fine, and make check passed all tests.
There are no files like liblwgeom.so anywhere on the server, just
liblwgeom.a and liblwgeom.h in /usr/local/src/postgis-1.5.2/liblwgeom/.

Any ideas greatly appreciated.

Thanks-

Lee


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

Re: [postgis-users] ST_Simplify

[Kevin Neufeld]

Hi Andy,

Can you post a small example?
-- Kevin


On 10/21/2010 10:02 AM, lplateandy wrote:

Hi,

I have, in the simplest example, a square polygon of 1 by 1 kilometer size
with nodes every 25 meters.

I can use ST_Simplify (the_geom,5) to remove most of the points.

However, for some reason i end up with the four corner nodes and a fifth
node 25m clockwise from the top left corner.

In itself, this would be fine - 5 nodes is better than 160.

However, on some of the polygons, there are more complex boundaries which i
wish to smooth out using simplify. I would do this with  ST_Simplify
(the_geom,26) example - going over the minimum 25 metres between  any nodes.

In the example of the square with 5 nodes, the problem i find then is that
the top left node is removed and the one 25m clockwise remains. The result
is that i no longer have a 1km square but sloping Western boundary correctly
going 1km North South but incorrectly veering across 25m to the East.

Is there a way to remove the stray node so i am more likely to maintain my
overall polygon shape. I did try st_simplifiypreservetopology but saw no
noticeable difference.

Hope that makes sense, any help much appreciated

Andy

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

[postgis-users] ST_Simplify

[lplateandy]

Hi,

I have, in the simplest example, a square polygon of 1 by 1 kilometer size
with nodes every 25 meters.

I can use ST_Simplify (the_geom,5) to remove most of the points.

However, for some reason i end up with the four corner nodes and a fifth
node 25m clockwise from the top left corner.

In itself, this would be fine - 5 nodes is better than 160.

However, on some of the polygons, there are more complex boundaries which i
wish to smooth out using simplify. I would do this with  ST_Simplify
(the_geom,26) example - going over the minimum 25 metres between  any nodes.

In the example of the square with 5 nodes, the problem i find then is that
the top left node is removed and the one 25m clockwise remains. The result
is that i no longer have a 1km square but sloping Western boundary correctly
going 1km North South but incorrectly veering across 25m to the East.

Is there a way to remove the stray node so i am more likely to maintain my
overall polygon shape. I did try st_simplifiypreservetopology but saw no
noticeable difference.

Hope that makes sense, any help much appreciated

Andy
-- 
View this message in context: 
http://old.nabble.com/ST_Simplify-tp30021514p30021514.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] Basic duplicating database question

[lplateandy]

Thanks Kevin, greatly appreciated. I think i'll make a lot of use of this...


Andy


===

I know, it looks complicated, but in a production system, this will be 
fast with minimal downtime (only during the final locking swap).

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



-- 
View this message in context: 
http://old.nabble.com/Basic-duplicating-database-question-tp30018606p30021440.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] Basic duplicating database question

[Kevin Neufeld]

On 10/21/2010 7:24 AM, lplateandy wrote:

The doc says "It also takes an exclusive lock on the specific index being
processed, which will block reads that attempt to use that index."

Does that mean that i'm not really any better off as the spatial index is
really the critical means controlling the reading of data into my GIS etc?


You could try to CREATE INDEX CONCURRENTLY, which won't block reads or 
writes.  There are some caveats, but that's dependent on your use cases.

http://www.postgresql.org/docs/9.0/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Rather than trying to reindex or cleaning up an existing index, you 
could try to create a second index (perhaps even concurrently) with a 
different name on your table.  Once complete you could DROP the old index.



What would be your scenario for a full vacuum?


Rather than performing a VACUUM FULL or CLUSTER (both will undesirably 
lock any production table, likely for several days on large tables), my 
approach would be to create a duplicate table with new indexes and 
silently switch the tables behind the scenes.


1. Create a duplicate table.  If you place the new table in a temp 
schema, you can use the exact same DDL as the oldtable.

CREATE SCHEMA tmp;
SET search_path TO tmp, public;
CREATE TABLE mytable (...);

2. Copy over the data
INSERT INTO mytable SELECT * FROM public.mytable;

3. Recreate your indexes.  This could even be done in parallel if you 
have more than one index to create.  I also temporarily bump up the 
memory parameters to create large indexes more quickly.  Again, the 
exact same DDL can be used as on your original table since this table 
exists in a different schema.  The DDL can be simply copied out of 
PgAdminIII.

-- temporarily set available memory to 1GB (if you have it)
SET maintenance_work_mem TO 1048576;
CREATE INDEX ... ON mytable ...;
...

4. In a single short locking transaction, perform the swap
CREATE SCHEMA tmp2;
BEGIN;
LOCK public.mytable;  -- will lock until end of transaction.
ALTER TABLE public.mytable SET SCHEMA TO tmp2;
ALTER TABLE tmp.mytable SET SCHEMA TO public;
-- Perhaps insert into the new table any insert statements
-- that that may have occurred since step 2 (perhaps based on a timestamp)
INSERT INTO public.mytable a
   SELECT * FROM tmp2.mytable b
   WHERE b.ts > (SELECT ts FROM public.mytable ORDER BY ts DESC LIMIT 1);
-- Perhaps deal with updates as well.
...
COMMIT;


I know, it looks complicated, but in a production system, this will be 
fast with minimal downtime (only during the final locking swap).


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

Re: [postgis-users] Basic duplicating database question

[Mike Toews]

On 21 October 2010 07:24, lplateandy  wrote:
>
> Hi Mike,
>
> OK - that's really useful. Does that only work for 9 or does it just happen
> you're pointing to the 9 document?
>
> The doc says "It also takes an exclusive lock on the specific index being
> processed, which will block reads that attempt to use that index."
>
> Does that mean that i'm not really any better off as the spatial index is
> really the critical means controlling the reading of data into my GIS etc?
>
> What would be your scenario for a full vacuum?

Oh right, there is a lock on reads to the index with REINDEX, so the
index is useless. I missed that detail. A table without a readable
index is probably no good for your situation.

Here is another helpful doc page to explain locks:
http://www.postgresql.org/docs/8.4/static/explicit-locking.html

Also, to change the doc version, just edit the URL to your version
(e.g., replace 8.4 with 8.2 or whatever).

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

Re: [postgis-users] Basic duplicating database question

[lplateandy]

Hi Mike,

OK - that's really useful. Does that only work for 9 or does it just happen
you're pointing to the 9 document?

The doc says "It also takes an exclusive lock on the specific index being
processed, which will block reads that attempt to use that index."

Does that mean that i'm not really any better off as the spatial index is
really the critical means controlling the reading of data into my GIS etc?

What would be your scenario for a full vacuum?

Thanks

Andy


Mike Toews-2 wrote:
> 
> On 21 October 2010 04:38, lplateandy  wrote:
>> Of course, even better would be a way to reindex whilst a database is in
>> use
>> but i'm under the impression that this is not possible at the moment?
> 
> If you DROP/CREATE your index then reads are blocked. But if you use
> REINDEX then only writes are blocked, while reads are still allowed
> using the old index.
> http://www.postgresql.org/docs/9.0/static/sql-reindex.html
> 
> -Mike
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Basic-duplicating-database-question-tp30018606p30020021.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] Basic duplicating database question

[Mike Toews]

On 21 October 2010 04:38, lplateandy  wrote:
> Of course, even better would be a way to reindex whilst a database is in use
> but i'm under the impression that this is not possible at the moment?

If you DROP/CREATE your index then reads are blocked. But if you use
REINDEX then only writes are blocked, while reads are still allowed
using the old index.
http://www.postgresql.org/docs/9.0/static/sql-reindex.html

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

[postgis-users] Basic duplicating database question

[lplateandy]

Hi,

I realise this is probably a very basic question but if anyone could point
me in the right direction i'd be very grateful.

I have a database which is just my data table and "geometry columns" and
"spatial_ref_sys" tables.

The data is of a significant volume with periodic updates which take days to
re-index

Apart from the updating (every month or so), read only access is the only
interaction.

Which is the best of the following options:

1) Run 2 copies of the database table and switch between them depending on
which is being updated
a) How would i create a copy of the table with the data it holds now
b) Is it better to create a copy within the same database or another on
the same server

2) Create a copy of the data and somehow change the table/database name
rather than
a) Is it ok to simply rename a database table so that any connecting
services just connect to a different table without being updated.
b) As for 1, i would need to know the best way to establish a copy



Of course, even better would be a way to reindex whilst a database is in use
but i'm under the impression that this is not possible at the moment?

Apologies again if this is a blindingly obvious question, didn't see
anything in the forums.

Thanks

Andy
-- 
View this message in context: 
http://old.nabble.com/Basic-duplicating-database-question-tp30018606p30018606.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

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

Re: [postgis-users] issue while building postgis-1.5.2 with pg-9

[Mark Cave-Ayland]

Dharmendra Goyal wrote:


Hi,

I am trying to build postgis-1.5.2 with postgresql-9.0.1 and i am 
getting following error while building the sources:


c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x47): 
undefined reference to `__security_cookie'
c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x539): 
undefined reference to `...@__security_check_cookie@4'


Error is coming just after executing:
make[1]: Entering directory 
`/c/pginstaller.pune-repo/postgis.windows/postgis'
dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll  --def 
libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o 
lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o 
lwgeom_inout.o lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o 
lwgeom_btree.o lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o 
lwgeom_box2dfloat4.o lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o 
lwgeom_export.o lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o 
lwgeom_in_gml.o lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o 
lwgeom_functions_lrs.o long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o 
geography_inout.o geography_gist.o geography_btree.o 
geography_estimate.o geography_measurement.o 
 -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition 
 -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib 
-L/C/pgBuild/zlib/lib -Wl,--as-needed   
-L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib 
-L/C/pginstaller.pune-repo/proj-4.6.1.staging/lib -lgeos_c -lproj 
-L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a 
-Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres


Looks like an MSVC-specific issue with the PROJ.4 library to me: 
http://blogs.msdn.com/b/nikolad/archive/2005/01/27/unresolved-external-security-cookie-with-platform-sdk-compiler.aspx



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] issue while building postgis-1.5.2 with pg-9

[Dharmendra Goyal]

Hi,

I am trying to build postgis-1.5.2 with postgresql-9.0.1 and i am getting
following error while building the sources:

c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x47):
undefined reference to `__security_cookie'
c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x539):
undefined reference to `...@__security_check_cookie@4'

Error is coming just after executing:
make[1]: Entering directory
`/c/pginstaller.pune-repo/postgis.windows/postgis'
dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll  --def
libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o
lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o
lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o
lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o
lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_export.o
lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o lwgeom_in_gml.o
lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o
long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o geography_inout.o geography_gist.o
geography_btree.o geography_estimate.o geography_measurement.o
 -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition
 -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib -L/C/pgBuild/zlib/lib
-Wl,--as-needed   -L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib
-L/C/pginstaller.pune-repo/proj-4.6.1.staging/lib -lgeos_c -lproj
-L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a
-Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres


There are many warnings like following, which i am assuming are not harmful:
Warning: .drectve `/manifestdependency:"type='win32'
name='Microsoft.VC90.CRT' version='9.0.21022.8' processorArchitecture='x86'
publicKeyToken='1fc8b3b9a1e18e3b'" /DEFAULTLIB:"MSVCRT"
/DEFAULTLIB:"OLDNAMES" ' unrecognized

Regards,
-- 
Dharmendra Goyal
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +91-20-30589493
Mobile: +91-9552103323

Website: http://www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are not
the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Measure distance to nearest border

[Nicklas Avén]

Ok still don't get the second query. If I understand you right you could try 
this: CREATE TABLE borddisttest2 ASselect a.gid, a.cell, 
MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM pgfinal2008 a, 
cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2008 AND 
b.gweyear >= 2008 and a.gwcode = c.gwcode and st_intersects(b.the_geom, 
c.the_geom)GROUP BY a.gid LIMIT 1;
Here I assume that every country only have one representation in cshape.What I 
do is that I try to only get the countries that intersects with the country 
that has the same gwcode as the cell you are calculating.  Here it is essential 
to have spatial index on the country-polygons and the centriod-pointYou should 
also have index on gwsyear, gweyear and gwcode in both pgfinal2008 and cshapes. 
I guess your limit 1 is just for testing purposes Probably you could make it 
quite a lot faster if you could trick it to inly do the intersection process 
once per country and not once per cell. Right now I don't see any good way to 
do that. Hopefully someone else sees a solution to that, or I will return if it 
comes to me. But maybe, if I recall right there were some discussions here that 
postgresql can cache results from one row to another. If soo it should work by 
itself or at least if you order your query by the gwcode so it takes all grid 
cells from one country first. /Nicklas 
2010-10-21 Andreas Forø Tollefsen wrote:

Ok. I will try to explain more in detail.>
I have a vector grid consisting of 64818 grid cells. Each of these cells are 
stored in the pgfinal2008 table, with two geometries variables: centroid 
(point) and cell (polygon).>
Each grid cell have a gwcode variable which is the country code. >

>>
In addition i have a table with all countries represented by polygons, and a 
table with all countries represented by their boundaries (line).>

>>
What i want to do is to measure the distance from each cell to the closest 
border..>

>>
While a simple measure of ST_Distance(pgfinal2008.centroid, cshapes.the_geom) 
WHERE pgfinal2008.gwcode != cshapes.gwcode will measure the distances, it will 
measure to all the polygons where the gwcode is different.>
However, I can apply a MIN() function to select the nearest of these, though 
this will take considerable time since every cell need to be measured to every 
country.>

>>
What i need to solve is how to limit the distance to measuring only to 
neighbouring countries of the country the cell is located.>

>>
Thanks for your help Nicklas.>

>>

>
>>
2010/10/21 Nicklas Avén 
> >
>hmm, I have to admit I don't really understand what you are trying to do, but 
>there are some thingsI don't think you really mean.>
> >select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM 
> >pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear 
> ><= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;>
> Why do you have, cshapes c, there. if you have 200 countries in it taking 
> away that will reduce the querytime to 1/200>What you get here is theclosest 
> country from your first grid cell.>
> >>
SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM 
borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gidGROUP BY 
pgfinal2008.cell, borddisttest2.gid;
>>This I don't get. Do you have many rows in pgfinal2008 with the same value in 
>>pgfinal2008.cell but different pgfinal2008.gid. What is "cell"? > >/Nicklas>
>
 >
> >
> 2010-10-21 Andreas Forø Tollefsen wrote:
>
> Thanks.>>
I have found a way to do this calculation.> >
However, since i gave 64818 cells and almost 200 countries, the distance from 
every cell to every country is calculated before the minimum distance is 
selected.> >
This takes awful lot of time.> >

> >> >
Any ideas on how to increase the performance?> >

> >> >
> >
DROP TABLE IF EXISTS borddisttest2;> >
CREATE TABLE borddisttest2 AS> >
select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM 
pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear 
<= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;> >

> >> >
DROP TABLE IF EXISTS borddist2008;> >
CREATE TABLE borddist2008 AS> >
SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM 
borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid> >
GROUP BY pgfinal2008.cell, borddisttest2.gid;
> >> >
2010/10/13 Nicklas Avén 
> > > >
> >Hi> > > >You can not cast to geography that way (if I haven't missed 
> >something essential)
> > And if you could you would mix geometry and geograph type in ST_Distance, 
> > which wouldn't work.> > > >St_Distance for geometry vs geography uses 
> > totally differnt algorithms. The geography calculations is far more 
> > advanced. > > > >ST_Shortestline only works for geometry type.> > > 
> > >/Nicklas> >
> >
 > >
> > > >
> > 2010-10-13 Andreas Forø Tollefsen wrote:
> >
> > Hi>> >

> > >> > >
Than

Re: [postgis-users] Measure distance to nearest border

[Andreas Forø Tollefsen]

Ok. I will try to explain more in detail.
I have a vector grid consisting of 64818 grid cells. Each of these cells are
stored in the pgfinal2008 table, with two geometries variables: centroid
(point) and cell (polygon).
Each grid cell have a gwcode variable which is the country code.

In addition i have a table with all countries represented by polygons, and a
table with all countries represented by their boundaries (line).

What i want to do is to measure the distance from each cell to the closest
border..

While a simple measure of ST_Distance(pgfinal2008.centroid,
cshapes.the_geom) WHERE pgfinal2008.gwcode != cshapes.gwcode will measure
the distances, it will measure to all the polygons where the gwcode is
different.
However, I can apply a MIN() function to select the nearest of these, though
this will take considerable time since every cell need to be measured to
every country.

What i need to solve is how to limit the distance to measuring only to
neighbouring countries of the country the cell is located.

Thanks for your help Nicklas.



2010/10/21 Nicklas Avén 

>  hmm, I have to admit I don't really understand what you are trying to do,
> but there are some thingsI don't think you really mean.
>
> select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM
> pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear
> <= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;
>
> Why do you have, cshapes c, there. if you have 200 countries in it taking
> away that will reduce the querytime to 1/200
> What you get here is theclosest country from your first grid cell.
>
>  SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance)
> FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid
> GROUP BY pgfinal2008.cell, borddisttest2.gid;
>  This I don't get. Do you have many rows in pgfinal2008 with the same
> value in pgfinal2008.cell but different pgfinal2008.gid. What is "cell"?
>
> /Nicklas
>
>
> 2010-10-21 Andreas Forø Tollefsen wrote:
>
> Thanks.>
> I have found a way to do this calculation.
> >
> However, since i gave 64818 cells and almost 200 countries, the distance
> from every cell to every country is calculated before the minimum distance
> is selected.
> >
> This takes awful lot of time.
> >
>
> >
> >
> Any ideas on how to increase the performance?
> >
>
> >
> >
> >
> DROP TABLE IF EXISTS borddisttest2;
> >
> CREATE TABLE borddisttest2 AS
> >
> select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM
> pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear
> <= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;
> >
>
> >
> >
> DROP TABLE IF EXISTS borddist2008;
> >
> CREATE TABLE borddist2008 AS
> >
> SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance)
> FROM borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid
> >
> GROUP BY pgfinal2008.cell, borddisttest2.gid;
>
> >>
> 2010/10/13 Nicklas Avén 
> > >
> >
> Hi
> >
>
> >
> You can not cast to geography that way (if I haven't missed something
> essential)
> > And if you could you would mix geometry and geograph type in ST_Distance,
> which wouldn't work.
> >
>
> >
> St_Distance for geometry vs geography uses totally differnt algorithms. The
> geography calculations is far more advanced.
> >
>
> >
> ST_Shortestline only works for geometry type.
> >
>
> >
> /Nicklas
> >
> >
>
> >
> >
>
> >
>
> > 2010-10-13 Andreas Forø Tollefsen wrote:
> >
> > Hi>
> >
>
> > >
> > >
> Thanks,
> > >
> I will give it a try.
> > >
> The reason I used the geography types in the distance query, were to get
> the distance in meters instead of degrees.
> > >
>
> > >
> >
> >
> Andreas
> > >
> > >> >
> >
> >
>
> >
> 2010/10/13 Nicklas Avén 
> > > > >
> > >
> Hi
> > >
>
> > >
> As I understand you you want to get the distance from each cell to the
> closest neighbour country. Then, why don't you just query the distance from
> your cell to closest country with other gwcode?
> > >
> Maybe something like:
> > >
>
> > >
> Create table borddisttest as
> > >
> Select ST_Distance(st_collect(b.the_geom), c.centroid)
> > >
> from cshapes a, cshapes b, pgfinal2008 c
> > >
> where a.gwcode=c.gwcode and b.gwcode != c.gwcode and
> st_intersects(a.the_geom, b.the_geom)
> > >
> group by c.gwcode and c.the_geom;
> > >
> > >
> With some tweaking like ordering the cells by gwcode I don't think the
> intersection calculation have to be done for each cell. Otherwise this is a
> very bad approach if there are many grid-cells.
> > >
>
> > >
> indexing on gwcode on both tables and spatial index on the country geoms
> for the intersection will also be of importance.
> > >
>
> > >
> I don't understand:
> > >
> geography(pgfinal2008.centroid)
> > >
>
> > >
> what does geography means here?
> > >
>
> > >
> I would also go for calculating the centroids on the fly. It should be fast
> and you will not get the trouble of updating the centroid column if
> adjustuing the grid.

Re: [postgis-users] Measure distance to nearest border

[Nicklas Avén]

hmm, I have to admit I don't really understand what you are trying to do, but 
there are some thingsI don't think you really mean. select a.gid, 
MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM pgfinal2008 a, 
cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= 2008 AND 
b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;
Why do you have, cshapes c, there. if you have 200 countries in it taking away 
that will reduce the querytime to 1/200What you get here is theclosest country 
from your first grid cell. 
SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM 
borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gidGROUP BY 
pgfinal2008.cell, borddisttest2.gid;
This I don't get. Do you have many rows in pgfinal2008 with the same value in 
pgfinal2008.cell but different pgfinal2008.gid. What is "cell"?  /Nicklas 
2010-10-21 Andreas Forø Tollefsen wrote:

Thanks.>
I have found a way to do this calculation.>
However, since i gave 64818 cells and almost 200 countries, the distance from 
every cell to every country is calculated before the minimum distance is 
selected.>
This takes awful lot of time.>

>>
Any ideas on how to increase the performance?>

>>
>
DROP TABLE IF EXISTS borddisttest2;>
CREATE TABLE borddisttest2 AS>
select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM 
pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear 
<= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;>

>>
DROP TABLE IF EXISTS borddist2008;>
CREATE TABLE borddist2008 AS>
SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM 
borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid>
GROUP BY pgfinal2008.cell, borddisttest2.gid;
>>
2010/10/13 Nicklas Avén 
> >
>Hi> >You can not cast to geography that way (if I haven't missed something 
>essential)
> And if you could you would mix geometry and geograph type in ST_Distance, 
> which wouldn't work.> >St_Distance for geometry vs geography uses totally 
> differnt algorithms. The geography calculations is far more advanced. > 
> >ST_Shortestline only works for geometry type.> >/Nicklas>
>
 >
> >
> 2010-10-13 Andreas Forø Tollefsen wrote:
>
> Hi>>

> >> >
Thanks,> >
I will give it a try.> >
The reason I used the geography types in the distance query, were to get the 
distance in meters instead of degrees.> >

> >> >
Andreas
> >
> >> >
>
>
 >
2010/10/13 Nicklas Avén 
> > > >
> >Hi> > > >As I understand you you want to get the distance from each cell to 
> >the closest neighbour country. Then, why don't you just query the distance 
> >from your cell to closest country with other gwcode?> >Maybe something 
> >like:> > > >Create table borddisttest as> >Select 
> >ST_Distance(st_collect(b.the_geom), c.centroid)> >from cshapes a, cshapes b, 
> >pgfinal2008 c> >where a.gwcode=c.gwcode and b.gwcode != c.gwcode and 
> >st_intersects(a.the_geom, b.the_geom)> >group by c.gwcode and c.the_geom;
> >> >With some tweaking like ordering the cells by gwcode I don't think the 
> >> >intersection calculation have to be done for each cell. Otherwise this is 
> >> >a very bad approach if there are many grid-cells.> > > >indexing on 
> >> >gwcode on both tables and spatial index on the country geoms for the 
> >> >intersection will also be of importance.> > > >I don't understand:> 
> >> >>geography(pgfinal2008.centroid)> > > >what does geography means here?> > 
> >> >> >I would also go for calculating the centroids on the fly. It should be 
> >> >fast and you will not get the trouble of updating the centroid column if 
> >> >adjustuing the grid.> > > >About doing calculations comparing geometries 
> >> >inside a dataset you use self join. > > > >You can join a table with 
> >> >itself as long as you put an alias so you can identify them like I did 
> >> >above with a, b and c> > > >Well, I am not sure I answered the right 
> >> >question but anyway...> >HTH> >Nicklas> >
> >
 > >
> >
> > 2010-10-13 Andreas Forø Tollefsen wrote:
> >
> > >> >
Hi all,> > >

> > >> > >
I have two datasets. On is a quadrate grid, and the other is the boundaries of 
all countries.> > >
What i want to do is to measure the distance from each centroid of the grid 
cells to the nearest border, but not all borders. Only the international.> > >

> > >> > >
First i convert my country polygon dataset into line features:> > >
> > >
select gwcode, gwsyear, gweyear, ST_Boundary(the_geom) AS line Into 
cshapes_line from cshapes;> > >

> > >> > >
Next i calculate the distance from the centroid of each cell to the nearest 
border where the gwcode (country code) of the cell is the same as the gwcode in 
the line feature.> > >
drop table if exists borddisttest;> > >
select pgfinal2008.cell, pgfinal2008.gwcode, 
ST_ShortestLine(pgfinal2008.centroid, st_boundary(cshapes.the_geom)) > > >
AS shortestline, ST_Distance(geography(pgfinal2008.centroid), 
st_boundary(cshapes.the

Re: [postgis-users] Measure distance to nearest border

[Andreas Forø Tollefsen]

Thanks.
I have found a way to do this calculation.
However, since i gave 64818 cells and almost 200 countries, the distance
from every cell to every country is calculated before the minimum distance
is selected.
This takes awful lot of time.

Any ideas on how to increase the performance?

DROP TABLE IF EXISTS borddisttest2;
CREATE TABLE borddisttest2 AS
select a.gid, MIN(ST_Distance(a.centroid, b.the_geom)) AS distance FROM
pgfinal2008 a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear
<= 2008 AND b.gweyear >= 2008 GROUP BY a.gid LIMIT 1;

DROP TABLE IF EXISTS borddist2008;
CREATE TABLE borddist2008 AS
SELECT pgfinal2008.cell, borddisttest2.gid, MIN(borddisttest2.distance) FROM
borddisttest2, pgfinal2008 WHERE borddisttest2.gid = pgfinal2008.gid
GROUP BY pgfinal2008.cell, borddisttest2.gid;

2010/10/13 Nicklas Avén 

>  Hi
>
> You can not cast to geography that way (if I haven't missed something
> essential)
> And if you could you would mix geometry and geograph type in ST_Distance,
> which wouldn't work.
>
> St_Distance for geometry vs geography uses totally differnt algorithms. The
> geography calculations is far more advanced.
>
> ST_Shortestline only works for geometry type.
>
> /Nicklas
>
>
> 2010-10-13 Andreas Forø Tollefsen wrote:
>
> Hi>
>
> >
> >
> Thanks,
> >
> I will give it a try.
> >
> The reason I used the geography types in the distance query, were to get
> the distance in meters instead of degrees.
> >
>
> >
> >
> Andreas
> >
> >>
> 2010/10/13 Nicklas Avén 
> > >
> >
> Hi
> >
>
> >
> As I understand you you want to get the distance from each cell to the
> closest neighbour country. Then, why don't you just query the distance from
> your cell to closest country with other gwcode?
> >
> Maybe something like:
> >
>
> >
> Create table borddisttest as
> >
> Select ST_Distance(st_collect(b.the_geom), c.centroid)
> >
> from cshapes a, cshapes b, pgfinal2008 c
> >
> where a.gwcode=c.gwcode and b.gwcode != c.gwcode and
> st_intersects(a.the_geom, b.the_geom)
> >
> group by c.gwcode and c.the_geom;
> >
> >
> With some tweaking like ordering the cells by gwcode I don't think the
> intersection calculation have to be done for each cell. Otherwise this is a
> very bad approach if there are many grid-cells.
> >
>
> >
> indexing on gwcode on both tables and spatial index on the country geoms
> for the intersection will also be of importance.
> >
>
> >
> I don't understand:
> >
> geography(pgfinal2008.centroid)
> >
>
> >
> what does geography means here?
> >
>
> >
> I would also go for calculating the centroids on the fly. It should be fast
> and you will not get the trouble of updating the centroid column if
> adjustuing the grid.
> >
>
> >
> About doing calculations comparing geometries inside a dataset you use self
> join.
> >
>
> >
> You can join a table with itself as long as you put an alias so you can
> identify them like I did above with a, b and c
> >
>
> >
> Well, I am not sure I answered the right question but anyway...
> >
> HTH
> >
> Nicklas
> >
> >
>
> >
> >
>
> > 2010-10-13 Andreas Forø Tollefsen wrote:
> >
> > >
> >
> Hi all,
> > >
>
> > >
> > >
> I have two datasets. On is a quadrate grid, and the other is the boundaries
> of all countries.
> > >
> What i want to do is to measure the distance from each centroid of the grid
> cells to the nearest border, but not all borders. Only the international.
> > >
>
> > >
> > >
> First i convert my country polygon dataset into line features:
> > >
> > >
> select gwcode, gwsyear, gweyear, ST_Boundary(the_geom) AS line Into
> cshapes_line from cshapes;
> > >
>
> > >
> > >
> Next i calculate the distance from the centroid of each cell to the nearest
> border where the gwcode (country code) of the cell is the same as the gwcode
> in the line feature.
> > >
> drop table if exists borddisttest;
> > >
> select pgfinal2008.cell, pgfinal2008.gwcode,
> ST_ShortestLine(pgfinal2008.centroid, st_boundary(cshapes.the_geom))
> > >
> AS shortestline, ST_Distance(geography(pgfinal2008.centroid),
> st_boundary(cshapes.the_geom))/1000 AS borddist
> > >
> into borddisttest
> > >
> from cshapes, pgfinal2008
> > >
> where cshapes.gwcode = pgfinal2008.gwcode;
> > >
>
> > >
> > >
> The problem here is that when using ST_Boundary, it converts all boundaries
> into lines, while i only want to get the boundaries where two polygons with
> different gwcodes meet.
> > >
> The lines where two countries meet are overlapping, meaning these borders
> have two gwcodes but in different line entries.
> > >
>
> > >
> > >
> Is there a way to calculate the overlaps within one dataset? If i could do
> this, then i could put a clause so the measurement only measures to lines
> with 2 or more gwcodes.
> > >
>
> > >
> > >
> Thanks.
>
> >___
> > postgis-users mailing list
> >postgis-users@postgis.refractions.net
> >postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> >
>
> ___
> p

Re: [postgis-users] How best to build a regular grid from points

[Andreas Forø Tollefsen]

I used the ST_Envelope function.
Create a field with the X and Y column of your points, and add something
like this:
ST_MakeEnvelope(xcoord-"+0.1+", ycoord-"+0.1+", xcoord+"+0.1+",
ycoord+"+0.1+", 4326))

The above is from my python and pygresql script so ignore the syntax.
However, the idea is the same if you want to create grid cells from points.

Andreas

2010/10/18 Randall, Eric 

>
>
> Hi Brent,
>
>
> I use something like this, should be adaptable to your use (but might not
> be the best way).
> this makes a 1 foot sided cell, and translates it 10x and 10y.
>
> Eric
>
>
>
> select st_translate(geom, 0 ,yseries) as geom from
> (select generate_series(0,9, 1) as yseries,st_translate(geom,
> xseries ,0) as geom from
> (select  generate_series(0, 9, 1) as xseries, geom from
> (select
> st_envelope(st_makeline(st_makepoint(138,63),st_makepoint(139,64)))
> as geom) as t1) as t2) as t3
>
>
>
>
> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of
> pcr...@pcreso.com
> Sent: Sunday, October 17, 2010 1:39 PM
> To: postgis-users@postgis.refractions.net
> Subject: [postgis-users] How best to build a regular grid from points
>
>
> Hi,
>
> I have a set of 155,000,000 points on a regular grid. I need to convert
> these to a set of square polygons covering the area represented by the
> points.
>
> Can anyone suggest how this could be done in Postgis?
>
> Thanks,
>
>  Brent Wood
> ___
> 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