Domenico Giusti | 27 Feb 20:24 2015
Picon

PostGIS 1.5.3-2 Help: ST_ConvexHull ( ST_Collect ( points from a join table

Dear list,

I have two tables and I need help:)

* table_record with a 3d polygon geom
	id, primary key
	code,
	geom
* table_record_point with a 3d point geom
	id, primary key
	code, reference table_record(code)
	x,
	y,
	z,
	geom

The relation is one-to-many, as one record could have one or more points.

I would like to update record.geom with a ConvexHull 3d polygon
collecting points from record_point.geom for some records.

I can print out the geom_hull:

SELECT p.code,
ST_ConvexHull(ST_Collect(p.geom)) AS geom_hull
FROM record_point AS p WHERE p.code = 'D19C5P05'
GROUP BY p.code;

but I miss the way to join the tables and update the record.geom.

(Continue reading)

Rémi Cura | 27 Feb 16:33 2015
Picon

QGIS plugin to track in PostGIS database (& trigger usage)

Hello Dear List,
We are pleased to announce the release of a QGIS plug-in that has been designed to be used with PostGIS.

The first functionality is that it allows multi-user camera extent (in qgis) tracking.
So when editing the same data, everybody knows where the others are editing, and there are no conflicts.
(example : each colour is a tracking of a different user, whose name has been added for understanding)




The second functionality is dedicated to PostGIS layer. It is a modification so that every change of a PostGIS layer is immediately sent to PostGIS (COMMIT).
This is most useful when the edited layers have triggers that change other geometric layers.
In this example, only the center line of the road is edited, yet the database recompute all the other geometries.
It is instantaneous for the user





It is available on the QGIS plug-in repository ("Interactive Map Tracking" is the name), or simply from QGIS in the plug-in manager.

We tried to provide a good user documentation.
We look forward to your comments/issues/enhancement!

Cheers,
Rémi-C and Yoyonel


_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
volker.emmel | 26 Feb 14:33 2015
Picon

overlapping geometries

hey list,

i have the following problem:

- i have overlapping geometries in one layer in my postgis (8.3) database.

-> how could i find the overlapping geometries??

->i could not divide into two layers...
-> if i have two layers i could use st_contain, but in one ??

- it will be over 6 million geometries....

any idea?

thanks
vollbtz
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Phil Hurvitz | 25 Feb 21:51 2015

Re: POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5)

Thank you, Bborie. This seems to be a partial solution (at least now 
PostGIS isn't complaining about the out-db raster). But now I get a 
different problem, which is that attempting to access the out-db raster 
makes the connection choke:

select st_summarystats(rast) from slope;
The connection to the server was lost. Attempting reset: Failed.
!>

-P.

**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
University of Washington, Seattle, Washington  98195-4802, USA
phurvitz <at> u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************

On 2/25/2015 12:41, Bborie Park wrote:
> Ah. I use a script similar to this.
>
> In the following block...
>
> start)
>      ...
>      su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>      ...
>
> You'll want to modify the su line to be like:
>
>      su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=1
> POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL $DAEMON -D '$PGDATA' &"
>  >>$PGLOG 2>&1
>
> See if that works...
>
> -bborie
>
>
> On Wed, Feb 25, 2015 at 12:29 PM, Phil Hurvitz <phurvitz <at> uw.edu
> <mailto:phurvitz <at> uw.edu>> wrote:
>
>     Certainly!
>
>     ! /bin/sh
>
>     # chkconfig: 2345 98 02
>     # description: PostgreSQL RDBMS
>
>     # This is an example of a start/stop script for SysV-style init, such
>     # as is used on Linux systems.  You should edit some of the variables
>     # and maybe the 'echo' commands.
>     #
>     # Place this file at /etc/init.d/postgresql (or
>     # /etc/rc.d/init.d/postgresql) and make symlinks to
>     #   /etc/rc.d/rc0.d/K02postgresql
>     #   /etc/rc.d/rc1.d/K02postgresql
>     #   /etc/rc.d/rc2.d/K02postgresql
>     #   /etc/rc.d/rc3.d/S98postgresql
>     #   /etc/rc.d/rc4.d/S98postgresql
>     #   /etc/rc.d/rc5.d/S98postgresql
>     # Or, if you have chkconfig, simply:
>     # chkconfig --add postgresql
>     #
>     # Proper init scripts on Linux systems normally require setting lock
>     # and pid files under /var/run as well as reacting to network
>     # settings, so you should treat this with care.
>
>     # Original author:  Ryan Kirkpatrick <pgsql <at> rkirkpat.net
>     <mailto:pgsql <at> rkirkpat.net>>
>
>     # contrib/start-scripts/linux
>
>     ## EDIT FROM HERE
>
>     # Installation prefix
>     prefix=/usr/local/pgsql
>
>     # Data directory
>     PGDATA="/usr/local/pgsql/data"
>     POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL
>
>     # Who to run the postmaster as, usually "postgres".  (NOT "root")
>     PGUSER=postgres
>
>     # Where to keep a log file
>     PGLOG="$PGDATA/serverlog"
>
>     # It's often a good idea to protect the postmaster from being killed
>     by the
>     # OOM killer (which will tend to preferentially kill the postmaster
>     because
>     # of the way it accounts for shared memory).  Setting the
>     OOM_SCORE_ADJ value
>     # to -1000 will disable OOM kill altogether.  If you enable this,
>     you probably
>     # want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that
>     # individual backends can still be killed by the OOM killer.
>     #OOM_SCORE_ADJ=-1000
>     # Older Linux kernels may not have /proc/self/oom_score_adj, but instead
>     # /proc/self/oom_adj, which works similarly except the disable value
>     is -17.
>     # For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0".
>     #OOM_ADJ=-17
>
>     POSTGIS_ENABLE_OUTDB_RASTERS=1
>     POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL
>
>     ## STOP EDITING HERE
>
>     # The path that is to be used for the script
>     PATH=/usr/local/sbin:/usr/__local/bin:/sbin:/bin:/usr/__sbin:/usr/bin
>
>     # What to use to start up the postmaster.  (If you want the script
>     to wait
>     # until the server has started, you could use "pg_ctl start -w" here.
>     # But without -w, pg_ctl adds no value.)
>     DAEMON="$prefix/bin/__postmaster"
>
>     # What to use to shut down the postmaster
>     PGCTL="$prefix/bin/pg_ctl"
>
>     set -e
>
>     # Only start if we can find the postmaster.
>     test -x $DAEMON ||
>     {
>              echo "$DAEMON not found"
>              if [ "$1" = "stop" ]
>              then exit 0
>              else exit 5
>              fi
>     }
>
>
>     # Parse command line parameters.
>     case $1 in
>        start)
>              echo -n "Starting PostgreSQL: "
>              test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
>     /proc/self/oom_score_adj
>              test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
>              su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>              echo "ok"
>              ;;
>        stop)
>              echo -n "Stopping PostgreSQL: "
>              su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
>              echo "ok"
>              ;;
>        restart)
>              echo -n "Restarting PostgreSQL: "
>              su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
>              test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
>     /proc/self/oom_score_adj
>              test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
>              su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>              echo "ok"
>              ;;
>        reload)
>              echo -n "Reload PostgreSQL: "
>              su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
>              echo "ok"
>              ;;
>        status)
>              su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
>              ;;
>        *)
>              # Print help
>              echo "Usage: $0 {start|stop|restart|reload|__status}" 1>&2
>              exit 1
>              ;;
>     esac
>
>     exit 0
>
>     -P.
>
>     ******************************__******************************__**
>     Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
>     Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
>     University of Washington, Seattle, Washington  98195-4802, USA
>     phurvitz <at> u.washington.edu <mailto:phurvitz <at> u.washington.edu> |
>     http://gis.washington.edu/__phurvitz
>     <http://gis.washington.edu/phurvitz>
>     "What is essential is invisible to the eye." -de Saint-Exupéry
>     ******************************__******************************__**
>
>     On 2/25/2015 12:25, Bborie Park wrote:
>
>         Can you post the shell script?
>
>
>
>         On Wed, Feb 25, 2015 at 11:50 AM, Phil Hurvitz <phurvitz <at> uw.edu
>         <mailto:phurvitz <at> uw.edu>
>         <mailto:phurvitz <at> uw.edu <mailto:phurvitz <at> uw.edu>>> wrote:
>
>              Thanks Bborie, I am starting with a shell script, and have
>         added the
>              env vars to that script, but am still unable to access the
>         out-db
>              rasters.
>
>              -P.
>
>
>         ******************************____****************************__**__**
>              Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
>              Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
>              University of Washington, Seattle, Washington  98195-4802, USA
>         phurvitz <at> u.washington.edu <mailto:phurvitz <at> u.washington.edu>
>         <mailto:phurvitz <at> u.washington.__edu
>         <mailto:phurvitz <at> u.washington.edu>> |
>         http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>
>              <http://gis.washington.edu/__phurvitz
>         <http://gis.washington.edu/phurvitz>>
>              "What is essential is invisible to the eye." -de Saint-Exupéry
>
>         ******************************____****************************__**__**
>
>              On 2/25/2015 11:45, Bborie Park wrote:
>
>                  The env variables need to be within the environment of
>         the postgres
>                  process. How are you starting postgres?
>
>                  Shell script? Then you should be able to add the
>         variables to
>                  that script.
>
>                  Direct invocation of posrgres on the command line? You
>         need to
>                  have the
>                  variables before the command
>
>                  VAR=1 postgres ...
>
>                  -bborie
>
>                  On Feb 25, 2015 11:40 AM, "Phil Hurvitz"
>         <phurvitz <at> uw.edu <mailto:phurvitz <at> uw.edu>
>                  <mailto:phurvitz <at> uw.edu <mailto:phurvitz <at> uw.edu>>
>                  <mailto:phurvitz <at> uw.edu <mailto:phurvitz <at> uw.edu>
>         <mailto:phurvitz <at> uw.edu <mailto:phurvitz <at> uw.edu>>>> wrote:
>
>                       Thanks Bborie, I built from the tarball rather
>         than using
>                  an rpm;
>                       does that still mean I should be adding the
>         environment
>                  variables to
>
>                       /etc/sysconfig/pgsql/______postgresql
>
>                       So for overkill I added the env vars to that file
>         as well
>                  as to the
>                       init script, restarted PostgreSQL, and it seems I
>         still cannot
>                       access the out-db raster.
>
>                       I added a slope raster using:
>
>                       raster2pgsql -I -C -e -Y -F -d -R -s 2926
>         ./slope/slope_ps.tif
>                       gis.slope | psql osm_test
>
>                       I can get metadata (sorry for the ugly text wrapping):
>
>                       select rid, (foo.md <http://foo.md>
>         <http://foo.md> <http://foo.md>).* from
>                  (select rid,
>                       st_Metadata(rast)  as md from slope) as foo;
>                         rid |    upperleftx    |    upperlefty    |
>         width | height |
>                       scalex      |      scaley       | skewx | skewy |
>         srid |
>                  numbands
>
>
>         -----+------------------+-----______-------------+-------+----__--__--__+------------------+--__----__----__---------+-------+__------__-+----__--+----------
>                           1 | 835161.301005914 | 758483.868026069 |
>         31935 |  34649 |
>                       32.8083333333333 | -32.8083333333333 |     0 |
>           0 | 2926
>                  |        1
>
>                       But cannot access values:
>
>                       select st_summarystats(rast) from slope;
>                       ERROR:  rt_raster_load_offline_data: Access to offline
>                  bands disabled
>                       CONTEXT:  SQL function "st_summarystats" statement 1
>
>                       select st_value(rast, 1, 1, 1) from slope;
>                       ERROR:  rt_raster_load_offline_data: Access to offline
>                  bands disabled
>
>                       -P.
>
>
>
>         ******************************______**************************__**__**__**
>                       Philip M. Hurvitz, PhD | Research Assistant
>         Professor | UW-CBE
>                       Urban Form Lab  | 1107 NE 45th Street, Suite 535
>         | Box 354802
>                       University of Washington, Seattle, Washington
>         98195-4802, USA
>         phurvitz <at> u.washington.edu <mailto:phurvitz <at> u.washington.edu>
>         <mailto:phurvitz <at> u.washington.__edu
>         <mailto:phurvitz <at> u.washington.edu>>
>                  <mailto:phurvitz <at> u.washington.
>         <mailto:phurvitz <at> u.washington.>____edu
>                  <mailto:phurvitz <at> u.washington.__edu
>         <mailto:phurvitz <at> u.washington.edu>>> |
>         http://gis.washington.edu/______phurvitz
>         <http://gis.washington.edu/____phurvitz>
>                  <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>>
>                       <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>
>                  <http://gis.washington.edu/__phurvitz
>         <http://gis.washington.edu/phurvitz>>>
>                       "What is essential is invisible to the eye." -de
>         Saint-Exupéry
>
>
>         ******************************______**************************__**__**__**
>
>
>                           Bborie Park dustymugs <at> gmail.com
>         <mailto:dustymugs <at> gmail.com>
>                  <mailto:dustymugs <at> gmail.com
>         <mailto:dustymugs <at> gmail.com>> <mailto:dustymugs <at> gmail.com
>         <mailto:dustymugs <at> gmail.com>
>                  <mailto:dustymugs <at> gmail.com <mailto:dustymugs <at> gmail.com>>>
>                           Wed Feb 25 10:46:59 PST 2015
>
>                        >
>
>                           Philip,
>
>                           POSTGIS_ENABLE_OUTDB_RASTERS=1 is an environment
>                  variable not to
>                           be in
>                           postgresql.conf.
>
>                           The same is true for
>                  POSTGIS_GDAL_ENABLED_DRIVERS=______ENABLE_ALL
>
>         https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>
>
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>>
>
>
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>
>
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux
>         <https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux>>>
>
>                           Based upon the above, it looks like you should add
>
>                           POSTGIS_ENABLE_OUTDB_RASTERS=1
>                           POSTGIS_GDAL_ENABLED_DRIVERS=______ENABLE_ALL
>
>                           to  /etc/sysconfig/pgsql/______postgresql
>
>                           The above assumes you're using the packages
>         provided by
>                  PostgreSQL.
>
>         http://www.postgresql.org/______download/linux/redhat/
>         <http://www.postgresql.org/____download/linux/redhat/>
>                  <http://www.postgresql.org/____download/linux/redhat/
>         <http://www.postgresql.org/__download/linux/redhat/>>
>
>           <http://www.postgresql.org/____download/linux/redhat/
>         <http://www.postgresql.org/__download/linux/redhat/>
>                  <http://www.postgresql.org/__download/linux/redhat/
>         <http://www.postgresql.org/download/linux/redhat/>>>
>
>                           -bborie
>
>
>
>                       On 2/25/2015 10:10, Phil Hurvitz wrote:
>
>                           Hi all, I am having trouble starting PostGIS with
>                  out-db raster
>                           support
>
>                           In my
>         /usr/local/pgsql/data/______postgresql.conf file I
>                  include the
>                           line
>
>                           POSTGIS_ENABLE_OUTDB_RASTERS=1
>
>                           after which PostGIS won't start (service
>         postgresql start).
>
>                           Software is
>
>                           postgis_full_version
>
>
>         ------------------------------______--------------------------__--__--__-----------
>                              POSTGIS="2.1.3 r12547"
>         GEOS="3.4.2-CAPI-1.8.2 r3921"
>                           PROJ="Rel. 4.7.1,
>                           23 September 2009" GDAL="GDAL 1.11.2, released
>         2015/02/10"
>                           LIBXML="2.7.6" TOPOLOGY RASTER
>
>
>                           Also PostGIS won't start when I specify
>
>                           POSTGIS_GDAL_ENABLED_DRIVERS=______ENABLE_ALL
>
>                           Any help would be appreciated!
>
>
>
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Phil Hurvitz | 25 Feb 19:10 2015

POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5)

Hi all, I am having trouble starting PostGIS with out-db raster support

In my /usr/local/pgsql/data/postgresql.conf file I include the line

POSTGIS_ENABLE_OUTDB_RASTERS=1

after which PostGIS won't start (service postgresql start).

Software is

postgis_full_version
-----------------------------------------------------------------------
  POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.7.1, 
23 September 2009" GDAL="GDAL 1.11.2, released 2015/02/10" 
LIBXML="2.7.6" TOPOLOGY RASTER

Also PostGIS won't start when I specify

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

Any help would be appreciated!

--

-- 
-P.

**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
University of Washington, Seattle, Washington  98195-4802, USA
phurvitz <at> u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
John Abraham | 25 Feb 18:49 2015

Re: ST_Intersection very slow

Wow guys.  131 seconds is a lot faster than 10 days.

Remi, if you can work out a fast intersection method that uses points and lines as a preprocessor to dealing
with the polygons, I think it would be a great addition to PostGIS.  ST_Intersection in PostGIS is often
quite a bit slower than the implementation in Geomedia, Mapinfo, and (I hear) ArcGIS, so any
functionality that results in speed improvements would be great.

Mark, I can't wait to figure out why your system was fast!  I was following your (preliminary) tutorial and
gridding the data was progressing very slowly.  

I have a provincial boundary file but there seems to be much ambiguity in GIS representations of the
provincial boundary, so I won't send you the one I have.  I can try to assemble one from other sources.

--
John Abraham
jea <at> hbaspecto.com
403-232-1060

On Feb 25, 2015, at 6:28 AM, Mark Wynter <mark <at> dimensionaledge.com> wrote:

> Hi John
> 
> I’ve just crunched your whole dataset.  The process takes 131 seconds for the vector tiling (using a 16
CPU machine).  Plus another 170 seconds for data prep at the start including making the poly's valid.
> For a 2 CPU machine, it will take circa 15 minutes, or double that using a single CPU.
> 
> Only one small issue outstanding - and that relates to clipping the regular grid prior to tiling.  For
clipping I used the union of the abmiw2wlcv_48tiles as supplied with the data - the problem is the
abmiw2wlcv_48tiles are rough and ready, which produces voids. The voids using my method unfortunately
get the same feature class as lc_class = 32.  You’ll see this clearly on second screenshot.
> The way around this is to clip the regular grid using a high-res shapefile of the Alberta state boundary
prior to the tile crunching the lancover_polygons_2010 table.  This is easily done - I just didn’t have
the state boundary data.
> 
> I need to get some sleep. John, Remi,  I will share with you the code tomorrow.  For others, I’ll be posting a
tutorial that steps through the methods touched upon in this thread..
> 
> John, the only difference between my tutorial and its application to your land cover data was a few tweaks
to the data prep stage. Otherwise the same code pattern (no modification at all needed to the
worker_function).  It was great to test the code with your data.
> 
> Speak tomorrow.
> Mark
> 
> 
> 
> 
> <Screen Shot 2015-02-25 at 11.29.15 pm.png>
> 
> 
> <Screen Shot 2015-02-25 at 11.39.04 pm.png>
> 
> 
> <Screen Shot 2015-02-25 at 11.41.41 pm.png>
> 
Joseph Spenner | 25 Feb 18:14 2015
Picon

TopologyException: geom is invalid

Hello,
I have a perl script which uses a geoJSON as input, and queries a table to see if any polygons exist in the table with any points in common.  If any matches are found, they are returned as geoJSON.  It's been working great, but yesterday I got an error.  Here's my query:

select ST_Asgeojson( geom ) from dynamic.polys where ST_Intersects(ST_GeomFromGeoJSON('$jsonPoly'), geom)=TRUE and ST_Area(ST_Intersection(ST_GeomFromGeoJSON('$jsonPoly')::geography, geom::geography))>500

With my current issue, the $jsonPoly is the state of Californina.   The select from dynamic.polys should return any polys in my database which have any points in common with California.

However, yesterday I had a query which resulted in the following error:

DBD::Pg::st execute failed: ERROR:  Error performing intersection: TopologyException: Input geom 1 is invalid: Self-intersection at or near point -381688.06935935974 -1206669.4272876547 at -381688.06935935974 -1206669.4272876547 at ./test.pl line 151.
DBD::Pg::st execute failed: ERROR:  Error performing intersection: TopologyException: Input geom 1 is invalid: Self-intersection at or near point -381688.06935935974 -1206669.4272876547 at -381688.06935935974 -1206669.4272876547 at ./test.pl line 151.

 ( line 151 is the perl code:   $sth->execute();   )

I isolated which JSON poly was causing the issue, and saved it here:   http://microflush.org/stuff/json/nwsZone.json

I verified the geoJSON, and verified it plots entirely within California using http://geojsonlint.com/

The state of Californina, which I got from NWS, can be found here:  http://microflush.org/stuff/json/CA.json

Can anyone see why this one nwsZone.json polygon is returning the error?


Any help would be great.

Thanks!

Regards,
Joseph Spenner


_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Ahmet Temiz | 25 Feb 13:10 2015
Picon

calculating area

Hello,

How  do we calculate the area (polygon) in country ( Turkey) level ?

Which one does give better result ? transforming to UTM or using geography ?

regards

--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Planlama ve Zarar Azaltma Dairesi Başkanlığı


________________________

Ahmet Temiz
Geological Eng.
Information Systems - GIS Group
Disaster and Emergency Management
of Presidency
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Mark Wynter | 24 Feb 23:10 2015

Re: ST_Intersection very slow


> Thanks Mark.  I will indeed do the st_dump, but I don't think it will help much (very few multis).  I think the
tiling will help a lot.  What I wonder, though, is how long it will take to tile?  Afterall, it's still an
st_intersection operation to tile each geometry against each tile.

I’ve almost finished writing the tutorial - where I address many of these points.    The variables that
affect performance are:
* how you’ve written your ST_Intersection query
* multi vs. non-multi
* size and complexity of geoms
* no. available CPUs (for parallelisation)
* tile batch size - important!!!

All strategies in combination may be necessary if your queries are taking forever.

For the demonstration dataset (a multi polygon representing whole of Australia), my tutorial tiling
query incorporates ST_Intersection and ST_Difference subqueries to produce tiled features
representing land and water.
I achieved a 49x reduction in the query time to tile the whole of Australia, starting with a single multi
polygon.  

The more complex the query, the more significant this time saving is in absolute terms.

> Is there a quad tree type tiling algorithm in a function?  If I do 256 x 256 tiles, doing it all at once would be
65536 operations of st_intersection(complex_geom, square).  With a quad tree I'll only have 4
operations of st_intersection(complex_geom, square), and then 16 of (a_little_less_complex_geom,
square), and then 64 of (even_less_complex_geom, square) and so on, for 8 nests.  The last one will still be
65536 operations, but the complex geoms should be a lot simpler by the time I get down to that level.  What do
you think, is this worth trying?  Or is intersecting with a square fairly simple regardless of how complex
the other geometry is?

I do have a SQL quadgrid tiling function - where a cell divides recursively subject to a maximum number of
levels or “value thresholds” - but I’m not sure if that’s the right approach.
Pierre Racine | 24 Feb 15:50 2015
Picon

ST_Clip on a raster without band crashes the server

Hi,

I have:

"POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL
1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"

and 

SELECT ST_Clip(ST_MakeEmptyRaster(42, 42, 0, 0, 1.0, 1.0, 0, 0, 4269), ST_MakeEnvelope(0, 0, 20, 20, 4269));

crashed my server.

Someone can confirm the bug?

Thanks,

Pierre
Rémi Cura | 20 Feb 14:30 2015
Picon

Re: operator is not unique: text || geometry

Sorry this it out of question.
You must be aware that I help people on the mailing list on my free time.
(I'm a researcher)

In my opinion, rewriting your function, then debugging it  is awfully close to proper work and not help.

This is particularly a problem because your question is not related to postgis improvement or other open source project.

I consider I already did more than help by giving you tools to understand your problem and solve it, then even rewrite your function.
There are many people on this mailing list that do this kind of function writing for a living (not my case).

If you have a specific question I may answer it.

Cheers,
RémiC

2015-02-18 23:29 GMT+01:00 Miller, Stephan <smille12 <at> harris.com>:

Here is the SQL.

 

Thanks.

 

Steve

 

From: Rémi Cura [mailto:remi.cura <at> gmail.com]
Sent: Wednesday, February 18, 2015 2:57 PM
To: Miller, Stephan; PostGIS Users Discussion


Subject: Re: [postgis-users] operator is not unique: text || geometry

 

(better to stay on list )
I meant
---
RAISE EXCEPTION '%',_q ;
---

You must understand that plpgsql function fabricate on the fly SQL statement (meaning, at execution time).

That means that without actually executing the function, there is no way to know exactly what it does.

Now i I __can't__ execute your function, not having your table

 

Now at execution,

it will stop you function there, and print the UPDATE query that should have been executed.

Then you can analyse the UPDATE query that have been printed, and test it to see why it doesn't work and how you could make it work (how which I have no idea without the query).

You should see something like (I put xxx because I don' have the value.)
---------

sql NOTICE :
UPDATE fgcm.xxxx SET (x,x,x,x)::topogeometry
= topology.toTopoGeom(ST_Transform(xxxxx::geometry,32648),'xxxx', 1, 1.0)                               

WHERE  objectid = xxxx'

---------

Maybe you need to replace the
-------
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0)

-------

with
---------
= topology.toTopoGeom(ST_Transform($1::geometry,32648), %L, 1, 1.0)
---------

I can't know.

Cheers,
Rémi-C

 

2015-02-18 20:43 GMT+01:00 Miller, Stephan <smille12 <at> harris.com>:

Remi –

 

I didn’t understand.

 

Adding RAISE EXCEPTION '%',-q ; before the EXECUTE generates a syntax error.  Did you mean perhaps

 

                                RAISE EXCEPTION '%',_q ;

Instead of

                                EXECUTE _q USING r.shape, cleantopo;

 

How do I specify the r.shape and cleantopo parameters?

 

Sorry to be so dense.

 

Thanks,

 

Steve

 

From: Rémi Cura [mailto:remi.cura <at> gmail.com]
Sent: Wednesday, February 18, 2015 1:59 PM


To: Miller, Stephan
Subject: Re: [postgis-users] operator is not unique: text || geometry

 

As I wrote before,

simply print the update query (and don't execute it)

You can do this by adding a RAISE EXCEPTION '%',-q ; before the EXECUTE

then test it !

Cheers,
Rémi-C

 

2015-02-18 19:57 GMT+01:00 Miller, Stephan <smille12 <at> harris.com>:

Remi –

 

I forced the transform as you suggested using SetSRID.   Now I am failing the UPDATE query somehow.  _________________________________________________________________________________________

                _q := format('SELECT objectid, f_code, shape, topo_shape FROM fgcm.%I',updatedtablename);

               

                FOR r IN

                                EXECUTE _q

                LOOP

                BEGIN

                                RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %' , r.objectid, r.shape, r.topo_shape;

                                RAISE NOTICE 'Table % Shape %', updatedtablename, r.topo_shape;

                                 _q :=

                                                format('UPDATE fgcm.%I SET %I = topology.toTopoGeom(ST_Transform(ST_SetSRID($1, 4326),32648), $2, 1, 1.0)

                                                                                WHERE  objectid = r.objectid' ,updatedtablename, r.topo_shape);

                                EXECUTE _q USING r.shape, cleantopo;

                                raise NOTICE 'After % Shape %',updatedtablename,r.topo_shape;

                    RAISE NOTICE 'Object % after conversion from shape = % to topo_shape = %', r.objectid, (ST_AsText(r.shape)), (ST_AsText(r.topo_shape));

                EXCEPTION

        WHEN OTHERS THEN

        RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;

                END;     

                END LOOP;

 

    RETURN;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 2000;

  

SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('vnroadsclipped', 'VNclippedroadscleantopo');

______________________________________________________________________________________

The results for the first feature is shown below.

 

NOTICE:  Loading 1 attempt with shape = 01020000E0E610000002000000380952E7B97B5A40F074DD1774CD344000000000006AE8C0000000000000F87FE825AB94B17B5A40F013885085CD344000000000006AE8C0000000000000F87F and topo_shape = <NULL>

NOTICE:  Table updatedvnroadsclipped Shape <NULL>

WARNING:  Loading of record 1 failed: 22004 null values cannot be formatted as an SQL identifier

 

The absence of the two RAISE NOTICE prints means the UPDATE is failing somehow.  Any suggestions?

 

Thanks,

 

Steve

 

From: Rémi Cura [mailto:remi.cura <at> gmail.com]
Sent: Wednesday, February 18, 2015 4:47 AM
To: Miller, Stephan


Subject: Re: [postgis-users] operator is not unique: text || geometry

 

Good,

maybe the srid of "$1::geometry" is not what it should be, you could try to force it (ST_SetSRID($1::geometry,your_srid)
ST_Transform($1::geometry, 32468)  --->  ST_Transform(ST_SetSRID($1::geometry,your_srid), 32468)
Cheers,
Rémi-C

 

2015-02-17 20:52 GMT+01:00 Miller, Stephan <smille12 <at> harris.com>:

Remi –

 

I have it working with one exception: my embedded ST_Transform($1::geometry, 32468) has stopped working.  It is not transforming lat/lon to a local UTM coordinate.

 

Thanks for your help!

 

Steve

 

From: Rémi Cura [mailto:remi.cura <at> gmail.com]
Sent: Tuesday, February 17, 2015 11:37 AM
To: Miller, Stephan
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] operator is not unique: text || geometry

 

Here is the "cleaned" version, still no good tough.

 

I don't understand what you want to do, I don't have your table structure nor your data, so you will need to work from that.

 

 

 

CREATE OR REPLACE FUNCTION hc_check_gaps_in_linear_topology(IN tablename text, IN cleantopo text)

  RETURNS TABLE(objectid integer, f_code character varying, topo_shape topogeometry) AS

$BODY$

declare

    updatedtablename text;

DECLARE

    r record;

    _q text; 

BEGIN

   

    -- SELECT sde_set_current_version(10.2.1);

 

            updatedtablename = 'updated' || tablename;

            _q := format('CREATE TABLE %I  AS SELECT objectid, f_code, shape  FROM %I ; ' ,updatedtablename,tablename) ;

            EXECUTE _q ;  

           

 

            PERFORM topology.DropTopology(cleantopo );

 

            -- Create a new topology

            -- Note need to generalize the SRID calculation to select the best fit UTM zone based on longitude extents

            PERFORM topology.CreateTopology(cleantopo ,32648, 0.000001, TRUE); 

            PERFORM  topology.AddTopoGeometryColumn( cleantopo , fgcm ,  updatedtablename ,'topo_shape','LINESTRING');

            PERFORM  topology.TopologySummary(cleantopo );

 

            _q := format('SELECT objectid, f_code, shape, topo_shape FROM fgcm.%I',updatedtablename); 

           

            FOR r IN

                        EXECUTE _q

            LOOP

            BEGIN

                        RAISE NOTICE 'Loading % attempt with shape = % and topo_shape = %' , r.objectid, r.shape, r.topo_shape; 

                        _q := 

                                    format('UPDATE fgcm.%I SET %I::topogeometry

                                                = topology.toTopoGeom(ST_Transform($1::geometry,32648), %I, 1, 1.0)

                                                            WHERE  objectid = $2' ,updatedtablename,topo_shape,cleantopo);

                       

                        EXECUTE _q USING r.shape, r.objectid ;

               

               RAISE NOTICE 'Object % after conversion from shape = % to topo_shape = %', r.objectid, (ST_AsText(r.shape)), (ST_AsText(r.topo_shape));  

            EXCEPTION

               WHEN OTHERS THEN

                        RAISE WARNING 'Loading of record % failed: % %', r.objectid, SQLSTATE, SQLERRM;

            END;

            END LOOP;

  

    RETURN;

 

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 2000; 

 

 

Cheers,

Rémi-C

 

 

 


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

Gmane