Garret W | 27 May 21:34 2016
Picon

topology - topogeo_id <> mylayer.mypk

Hi all,

After creating a topology on a trail network, the unique IDs used in my dataset are not the showing up as they should in relations.topogeo_id. 

I tested the data out on a new test table I created directly from the production data and everything worked fine. 

Here are my steps (pretty standard):

SELECT CreateTopology('trails_topo', 3435);
SELECT AddTopoGeometryColumn('trails_topo', 'myschema', 'temp_trails', 'topogeom', 'LINESTRING');
UPDATE public.temp_trails SET topogeom = toTopoGeom(geom, 'trails_topo', 1, 3);

Afterwards Im just using the relations table to join the attributes to the new topological data.

Ive been able to workaround the issue for now. But I thought I would bring this up to the user group, hoping someone with better understanding could help confirm whether it is a bug within the topology extension or user error.

Here is my setup:
Ubuntu 14.04.3 LTS

Postgres 9.5

POSTGIS="2.2.1 r14555" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0
.11.99" TOPOLOGY RASTER

Thanks for the help
Garret
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Daniel Migowski | 27 May 08:17 2016
Picon

Compile PostGIS for PostgreSQL 9.5 64bit Windows without VC

Hello,

 

is there a possibility to commercially compile PostGIS for the PostgreSQL 9.5 64bit Distribution by Enterprise DB without having to install the commercial version of Visual Studio 2015? Maybe by using MinGW?

 

Regards,

Daniel Migowski

 

IKOffice

UNTERNEHMENSSOFTWARE

IKOffice GmbH

Daniel Migowski

Mail:

dmigowski <at> ikoffice.de

Marie-Curie-Straße 1

Tel.:

+49 (0)441 21 98 89 52

26129 Oldenburg

Fax.:

+49 (0)441 21 98 89 55

http://www.ikoffice.de

Mob.:

+49 (0)176 22 31 20 76

Geschäftsführer:

Ingo Kuhlmann, Daniel Migowski

Amtsgericht Oldenburg:

HRB 201467

Steuernummer:

64/211/01864

 

Die IKOffice GmbH ist auch in diesem Jahr auf den einschlägigen Messen präsent!

Auf www.ikoffice.de finden Sie alle Einzelheiten zu den jeweiligen Veranstaltungen.

Gern senden wir Ihnen kostenlose Eintrittskarten per Mail. Wir freuen uns auf Ihren Besuch!

 

                                                     

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Michael Treglia | 27 May 04:33 2016
Picon

Topology Issue Reported by Query but Not Identified with ST_IsValid

Hi All,

I was running a query involving ST_Difference between two multipolygon layers, and a while in, it threw this message: 
ERROR: GEOSDifference: TopologyException: side location conflict at 919670.1768945494 140665.82566365649

However, I previously ran ST_MakeValid, and ST_IsValid doesn't report any objects with problems. Is there something I'm missing, or something else I need to check? (Apologies is this is just a naive question - still learning my way around PostGIS).

And in case it helps, here's my SQL:
 
CREATE TABLE SI_Unclaimed AS
SELECT gid, COALESCE(ST_Difference(geom_2263, (SELECT ST_Collect(b.geom_2263) 
                                         FROM citywide_basedata.parcels15 b
                                         WHERE ST_Intersects(a.geom_2263, b.geom_2263)
                                         )), a.geom_2263)
FROM citywide_basedata.boroughs_nowater a where boroname like 'Staten Island';

Thanks!
Mike
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Chris B | 27 May 02:04 2016
Picon

Point In Polygon Update Query

I'm trying to put what the warning is for the point in the polygon, I have my wwmaster which is where all the warnings are stored, my mastergistmp where my users are stored, I know if a user is in a severe thunderstorm warning and a tornado warning the tornado warning will override the severe storm warning which is what I want does anyone see anything wrong with this I think I am missing some users in the warned area. everything is epsg 4326 I would like to also make it where users with in a mile of the warned area are updated with the information I've tried st_buffer and have had trouble with it. I'm updating the mastergistmp.warn or thats what I'm shooting for.

UPDATE mastergistmp SET warn = wwmaster.prod_type FROM wwmaster WHERE  wwmaster.prod_type = 'Flash Flood Warning' and ST_Within(mastergistmp.geom,wwmaster.geom);
UPDATE mastergistmp SET warn = wwmaster.prod_type FROM wwmaster WHERE  wwmaster.prod_type = 'Severe Thunderstorm Warning' and ST_Within(mastergistmp.geom,wwmaster.geom);
UPDATE mastergistmp SET warn = wwmaster.prod_type FROM wwmaster WHERE  wwmaster.prod_type = 'Tornado Warning' and ST_Within(mastergistmp.geom,wwmaster.geom);

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Jean-François Gigand | 26 May 11:18 2016
Picon
Gravatar

Future of the 1.5x release branch?

Hi,

About the good old 1.5.x branch of PostGIS, even if outdated,
is it still supported about bug fixes, and more importantly: security fixes?

I could not find this information on the mailing list archive nor on the web.
Did I miss the information somewhere?

Thanks,

Jean-François Gigand - Geonef
Paris, France - http://geonef.fr/
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Paolo Crosato | 26 May 11:04 2016
Picon

Postgis and pgpool replication

Hi,

I'm evaluating a pgpool cluster with 2 nodes as a substitute for the single postgresql instance we are using now.

Has anyone any experience to share about using postgis in a pgpool cluster? Is it transparent or are there any particular issues I should take care of?

Thanks for any response,

Regards

Paolo Crosato
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Timothy Asquith | 25 May 03:31 2016
Gravatar

Re: ST_Intersects differing results

Thanks Paul,

You’re absolutely right that the intersect query should always have returned touching polygons. I’m not sure how we were ever getting the desired results. That’s what I’m looking into now.

Interestingly, the critical difference seems to be the use of binary geometry.

The land parcel geometry is passed into the URL, and used as the basis for the intersect query:

http://my-geoserver.example.com/geoserver/workspace/wfs?service=WFS&version=2.0.0&request=GetFeature&typeName=workspace:zones&outputFormat=json&cql_filter=INTERSECTS(geom,POLYGON((-37.79397716231438%20145.0752696285534,-37.79395266253229%20145.07506684589112,-37.79391636418757%20145.07507518332602,-37.79354819089359%20145.07515979038527,-37.79358130702303%20145.0753488489579,-37.79397716231438%20145.0752696285534)))

Looking at my Postgres query logs, this generates the following query:

SELECT
"feature_id",
"scheme_code",
"zone_status",
"zone_code",
"updated_at",
encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM
"public"."planning_zones"
WHERE
"geom" && ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)
AND ST_Intersects("geom", ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)) LIMIT 1000000;

This query provides the current behaviour - returning a single zone polygon that intersects with the parcel polygon.

However, if I take that same polygon string, and run the query using ST_AsText:

SELECT
"feature_id",
"scheme_code",
"zone_status",
"zone_code",
"updated_at",
encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM
"public"."planning_zones"
WHERE
"geom" && ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326)
AND ST_Intersects("geom", (ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326))) LIMIT 1000000;

Then I get zero results.

The parcel geometry in the URL comes from the same database. If I rewrite this query to use a spatial join, as detailed in my previous email, I get 3 intersecting zone polygons, which sounds like the correct behaviour.

Is there a tolerance / precision difference between text and binary geometry?

I’m now looking for workarounds or solutions - stop me if I’m heading in the wrong direction:

- workaround: take the zone polygon with the largest intersecting area
- workaround: filter out polygons below an arbitrary intersection area (i.e. anything with <1m2 area isn’t useful)
- solution: snap my zone polygons to my parcel polygon, and filter out results that only share a border
    - I’m not experienced in doing this
    - I’m experimenting with ST_Snap and filtering out ST_Touches, but it still returning the same 3 results 

Thanks for all the help

▬▬▬ι══════════ﺤ
Timothy Asquith // Red Ronin
tim <at> redronin.io
www.redronin.io

On 25 May 2016 at 5:00:52 AM, postgis-users-request <at> lists.osgeo.org (postgis-users-request <at> lists.osgeo.org) wrote:

Re: ST_Intersects differing results
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Timothy Asquith | 24 May 15:45 2016
Gravatar

ST_Intersects differing results

I have a table of land parcels in my PostGIS database, and I'm doing a basic spatial join against zoning polygons.

I'm having issues where a previously-correct query is now returning different results.

Up until now, I've been using a Geoserver WFS, and making these queries using CQL's INTERSECT operation. This operation generates a PostGIS query like so:

SELECT * FROM "public"."planning_zones" WHERE "geom" && ST_GeomFromWKB($1, 4326) AND ST_Intersects("geom", ST_GeomFromWKB($2, 4326)) LIMIT 1000000

As my land parcel dataset is snapped to my zoning polygons, this query causes Geoserver to return a single record.

Now, I'm refactoring that query into a JSON API I'm building, and I'm recreating that PostGIS query, and making the query directly to Postgres (not Geoserver).

The problem is that the query is now returning multiple zoning polygons. The difference is that I'm directly joining the zoning polys to the parcels:

SELECT * FROM zones AS z, parcels AS p ON ST_Intersects(z.geom, p.geom) WHERE p.id = 99 AND z.geom && p.geom

This new query is returning multiple results, including polygons that share a boundary. This didn't happen before.

Can anyone shed any light on why this might be happening? My suspicions are that the tolerance of the Geoserver query differs to the raw PostGIS query, but I'm uncertain how to best affect this.

▬▬▬ι══════════ﺤ
Timothy Asquith // Red Ronin
tim <at> redronin.io
www.redronin.io
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Jan Michálek | 22 May 23:19 2016
Picon

POINT/MULTIPOINT EMPTY problem in dump

I need to solve this problem

I have in table column POINT, some of values ARE 'POINT EMPTY' (dont ask me why, it is not my table).
MULTIPOINT EMPTY and POINT EMPTY has same representation (010400000000000000).
If i try restore table from dump, it is problem because postgis read this as multipoint empty, but the column has type POINT.
My version of postgis

jelen=# select postgis_version();
            postgis_version           
---------------------------------------
 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Postgre is 9.3.

Is there somebody who knows how to solve this problem?

Thanks Je;

----------
jelen=# CREATE TABLE t3 (id serial primary key, geom geometry(POINT, 0));
CREATE TABLE
jelen=# insert into t3(geom) VALUES ('POINT EMPTY'::geometry);
INSERT 0 1
jelen=# SELECT geom from t3;
        geom       
--------------------
 010400000000000000
(1 row)

jelen=# SELECT ST_AsText('010400000000000000');
    st_astext    
------------------
 MULTIPOINT EMPTY
(1 row)

jelen=# INSERT INTO t3(geom) values('010400000000000000');
ERROR:  Geometry type (MultiPoint) does not match column type (Point)
jelen=# insert into t3(geom) SELECT geom FROM t3;
INSERT 0 1
jelen=#
-------------------------------


--
Jelen
Starší čeledín datovýho chlíva
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
Paul Norman | 19 May 00:36 2016
Picon
Gravatar

Making GeoHash ordering faster

Ordering tables by ST_GeoHash is a common technique to make them 
spatially correlated on disk. During recent tests with osm2pgsql on a 
machine with very fast disks, it was found over 20% of CPU time was 
spent comparing strings.

This is because doing ORDER BY ST_GeoHash(...); does sorting in the 
locale of the database, normally some language in UTF8. Because 
ST_GeoHash returns strings in base-32 only using 0-9 b-z, the C 
collation can be specified instead, allowing for much faster string 
comparisons.

This can be done with ORDER BY ST_GeoHash(...) COLLATE "C";

The fastest ordering I am aware of is ORDER BY 
ST_GeoHash(ST_Transform(ST_Envelope(geom),4326),10) COLLATE "C";. This 
is faster than simply ORDER BY ST_GeoHash(ST_Transform(geom,4326)); by 
about 40% in some cases.

I wrote some more in the blog post

http://paulnorman.ca/blog/2016/05/improve-your-st-geohash-sorting-with-these-three-simple-tricks/, 
but the essentials are above.
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
David Haynes | 18 May 17:35 2016
Picon

PL/Python

Hello,

I have a question regarding importing the gdal library using pl/python. Specifically, I want to create an in memory raster using the gdal library. This is a snippet of code that I have written,

CREATE OR REPLACE FUNCTION terrapop_area_level_to_raster2( sample_geog_level_id bigint, raster_variable_id bigint, raster_band bigint) RETURNS text AS
$BODY$   
from osgeo import gdal
import numpy as np
from osgeo import ogr, osr

memDriver = gdal.GetDriver(19)
#memDriver = gdal.GetDriverByName('MEM')
memRast = memDriver.Create('', 10, 10, 1, gdal.GDT_Int32)

This is the error I receive. A python NoneType error. Which seems to that the gdal class has not been imported. I have verified that gdal is available on the system. 

ERROR: AttributeError: 'NoneType' object has no attribute 'Create'
SQL state: XX000

However, this function on the same server and database returns to me all the gdal drivers. Any idea how I can diagnose this problem?

CREATE OR REPLACE FUNCTION gdal_drivers()
  RETURNS SETOF text AS
$BODY$

from osgeo import gdal
driver = gdal.GetDriverByName('MEM')
rast = driver.Create('', 2, 4, 1, gdal.GDT_Int32)

names = []
driverall = gdal.GetDriverCount()
for i in range(gdal.GetDriverCount()):
driver = gdal.GetDriver(i)
drivername = driver.ShortName
names.append([i,drivername])

return names

$BODY$
  LANGUAGE plpythonu VOLATILE





_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Gmane