Preuß, Sylvia | 31 Jul 11:29 2014
Picon

PG 2.0 to add table with heterogeneous geometry in QGIS

Hello list,

 

I have heterogeneous geometry (Polygon and MultiPolygon ) in one single table.

 

CREATE TABLE myschema.mytable

(  gid serial NOT NULL,

    the_geom geometry(geometry,25832),

CONSTRAINT pkey_mytable  PRIMARY KEY (gid)

);

 

Unfortunately I can’t add this as a PostGIS –Layer in QGIS. The geometry is not found.

 

Same behaviour with

CREATE TABLE myschema. mytable

(

 gid serial NOT NULL,

  the_geom geometry,

CONSTRAINT pkey_ mytable PRIMARY KEY (gid)

);

 

I can’t do it like this:

 CREATE TABLE myschema. mytable

(  gid serial NOT NULL,

  the_geom geometry(Polygon OR MultiPolygon,25832),

CONSTRAINT pkey_ mytable PRIMARY KEY (gid)

)

 

PostGIS version:

2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Postgres version:

PostgreSQL 9.2.8, compiled by Visual C++ build 1600, 64-bit

QGIS 2.4.0

 

Sylvia


Der E-Mail-Dienst des Oberbergischen Kreises dient ausschließlich der dienstlichen Kommunikation.
Senden Sie deshalb keine E-Mails privaten Inhalts an E-Mail-Adressen des Oberbergischen Kreises.
Es wird darauf hingewiesen, dass neben der Person, an die Ihre E-Mail gerichtet ist, auch deren Vertretung im Amt einen unmittelbaren Zugriff auf Ihre Nachricht hat. Für Berufsgeheimnisträger und besondere Funktionsträger gelten abweichende Regelungen.
Es wird außerdem darauf hingewiesen, dass die Kommunikation per E-Mail ohne Authentifizierung und Verschlüsselung unsicher ist, da für unberechtigte Dritte grundsätzlich die Möglichkeit der Kenntnisnahme und Manipulation besteht.
Es wird deshalb keine Verantwortung für den Inhalt dieser Nachricht übernommen, da eine Manipulation nicht ausgeschlossen werden kann.
Obwohl alle angemessenen Vorkehrungen getroffen wurden, um sicherzustellen, dass Anlagen dieser E-Mail virusgeprüft sind, wird empfohlen, anhängende Dateien vor dem Öffnen durch Ihr eigenes Virus-Programm zu prüfen, da keinerlei Haftung für Schäden übernommen wird, die infolge etwaiger Software-Viren entstehen könnten.
Der Inhalt dieser E-Mail ist ausschließlich für die bezeichnete Person bestimmt. Wenn Sie nicht der vorgesehene Adressat dieser E-Mail oder dessen Vertretung sein sollten, beachten Sie bitte, dass jede Form der Kenntnisnahme, Veröffentlichung, Vervielfältigung oder Weitergabe des Inhalts dieser E-Mail unzulässig ist. In diesem Fall wird darum gebeten, sich mit der absendenden Person der E-Mail in Verbindung zu setzen.
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
G. Allegri | 30 Jul 17:14 2014
Picon

polyhedral surface import from common 3D formats

I'm looking for a tool to translate some common formats (OBJ, PLY, STL, etc) to a WKB/WKT representation of polyhedral surfaces.
I haven't found anything. Is anybody aware of something similar? 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Alex Reitbort | 29 Jul 12:05 2014

Postgis intersect query with limit does not use spatial index

Hello,

 

I am  using Postgre 9.3.4 with postgis 2.1.3

"PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"

 

I have a spatial table with 100 000 000 rows. This table has two columns sgid (primary key) and way(geometry(MultiPolygon,4326)). There is a spatial index on the spatial column.

 

What I want to know if there are any rows in specified rectangular bbox. For this I tried using && spatial operator with limit clause.

Here is a my query:

select * from "public"."osm_buildings" where "public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375 35.5078125,-101.6015625 35.68359375)'::box2d,4326) limit 1;

 

When I execute this query without limit clause, it uses the spatial index and returns me the results in 20ms.

This is the results of explain analyze

"Bitmap Heap Scan on osm_buildings  (cost=876627.31..4408250.75 rows=21800115 width=160)"

"  Recheck Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"

"  ->  Bitmap Index Scan on osm_buildings_index  (cost=0.00..871177.29 rows=21800115 width=0)"

"        Index Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"

 

 

However when I execute this query with limit clause it decides to do a full table scan without using the index and takes more than 15 seconds to finish.

This is the explain analyze for query with limit clause:

"Limit  (cost=0.00..0.21 rows=1 width=160)"

"  ->  Seq Scan on osm_buildings  (cost=0.00..4621629.20 rows=21800115 width=160)"

"        Filter: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"

 

 

How can I force the query with limit to use the index? What query can I use to check if there is any data in required bbox?

 

 

Alex Reitbort

Software Developer

Skyline Software Systems Inc.

www.skylineglobe.com

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Luís de Sousa | 25 Jul 10:03 2014
Picon

PostGIS non functional on Ubuntu 14.04

Dear all,

I have the following packages installed on Ubuntu 14.04 (all fetched
with apt from the repositories):
. postgresql-9.3
. postgresql-9.3-postgis-2.1
. libgeos-3.4.2

When I try to add the PostGIS extension to a database I get following message:

$  psql pg_testing -c "create extension postgis"
ERROR:  could not load library
"/usr/lib/postgresql/9.3/lib/postgis-2.1.so":
/usr/lib/liblwgeom-2.1.3.so: undefined symbol:
GEOSDelaunayTriangulation

I have posted full package details at GIS.SE:
http://gis.stackexchange.com/questions/108308/postgis-non-functional-on-ubuntu-14-04

Apparently, there is some mismatch between libgeos and postgis. But I
have the exact same versions indicated in the compile guide at the
Wiki:
http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1310src

Any advice on how to get PostGIS running is welcome. Thank you,

Luís
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Rolland Jérôme | 23 Jul 10:03 2014
Picon

A PostGIS 3D Viewer

Hello PostGIS Community,

Have a look to my blog at the following address  http://ageoguy.blogspot.fr/2014/07/postgis-3d-viewer.html
 
This tutotrial which present a tool to view 3D geometries from a PostgreSQL/PostGIS database.
















Regards
Jérôme

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Patricio Gigoux | 22 Jul 19:05 2014
Picon

how can i convert xz in xy?

Hellow:
How could I convert from elevation xz in a pollygon xy, where I conserv the feature projection, is how a type of explode from 3d to 2d.
Whith Postgis obviously.
Thanks in advance

Patricio Gigoux Méndez
Ingeniero Civil Informático
Dirección de Obras Municipales - DOCTEC
I. Municipalidad de Santiago
Fono: 27136284

Email:pgigoux <at> munistgo.cl
---------------------------------

 

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Dave Barter | 23 Jul 15:40 2014
Picon

PostGis Raster Merge with polygon

Hi

Wonder if anyone on the list can help with this problem, it is quite involved so posted to stack exchange


Thanks
Dave

-- 
Dave Barter

Web: http://www.phased.co.uk
Email:dave <at> phased.co.uk
Twitter: <at> Citizenfishy
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Raffaele Morelli | 23 Jul 11:49 2014
Picon

can't restore dump in postgis-2.1.3

Hi,

I upgraded my postgres/postgis installations but loading the dump from the production server results in tons of errors as these:

pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.0": No such file or directory

pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-1.5": No such file or directory

I am running debian (testing/jessie) with postgis-2.1.3 and postgresql-9.3

$ pg_config --libdir
/usr/lib/x86_64-linux-gnu


Any suggestions?
Regards
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
j.rolland | 23 Jul 10:29 2014
Picon

A PostGIS 3D Viewer

Hello PostGIS Community,

Have a look to my blog at the following address  
http://ageoguy.blogspot.fr/2014/07/postgis-3d-viewer.html
<http://ageoguy.blogspot.fr/2014/07/postgis-3d-viewer.html>   
This tutotrial which present a tool to view 3D geometries from a
PostgreSQL/PostGIS database.

<http://postgis.17.x6.nabble.com/file/n5006710/pg3DViewer_title_small.png> 

<http://postgis.17.x6.nabble.com/file/n5006710/CAPTURE_001.jpg> 

Best regards,
Jérôme

--
View this message in context: http://postgis.17.x6.nabble.com/A-PostGIS-3D-Viewer-tp5006710.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Richard Greenwood | 23 Jul 03:17 2014
Picon

topology, joining attributes

I have a table of simple polygons named work.aliquot with a primary key named ogc_fid and several columns of attribute data. I created a topology and added a topogeometry column and then:

UPDATE aliquot SET topogeom = toTopoGeom(wkb_geometry, 'aliquot_topology', 1);

The various fields and tables all appear to have been populated correctly.

Now I need to join the attributes from the work.aliquot table to aliquot_topology.face but I'm stuck. I was guessing that aliquot_topology.relation would be the route but after some experimenting that seems not to be the case, or else I'm not getting it. Do I have to do a spatial join to get my attributes from the source table into my new topology?

Thanks,
Rich

--
Richard W. Greenwood, PLS
www.greenwoodmap.com
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Sparr | 18 Jul 16:20 2014
Picon

Using PostGIS for partial intersection/union of many polygons

On Wed, Jul 31, 2013 at 5:54 AM, Rémi Cura <remi.cura <at> gmail.com> wrote:
> Hey,
> my 2 cents (sorry I can't access your example)
>
> Depending on what you want, you may have interest in sampling :
> you create a grid of point (there is a function) or a grid of polygon
> square.
> Then you compute for each point(square) the number of polygon it is in (on
> simple sql query invovling count(*) OVER and st_intersects)
> Then when you want space where there are between N and M polygons
> overlaying, you just query the table with point/square with a WHERE count >N
> and count <M.
>
> It should run very fast, even fater if you put btree index on the count
> result.
> I don't know what you want, but if this is some kind of indicator, sampling
> may be legitimate.
> If you want crisp boundary, it may be used to fasten computing (doing the
> precise computing only on polygons on border)

I would prefer not to resort to sampling. Some of the boundaries are
drawn arbitrarily precisely, and I'd like to preserve that precision.

http://regionaldifferences.com/results.html?region=New%20England&lat=42&lon=-73&zoom=6
(now with 708 polygons)
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane