Casper Børgesen (CABO | 16 Dec 16:18 2014
Picon

Why won’t my SELECT query use the INDEX?

This seems like one of the most asked questions in the PostgreSQL world, but I guess I haven’t understood all the answers yet:

 

Why won’t my SELECT query use the INDEX I have created for it?

 

I have a table with about 18mio rows.

 

My SELECT statement looks like this:

 

SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"

FROM my_table

WHERE geom &&

ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))', ST_SRID("geom"))

 

The EXPLAIN ANALYZE of the above statement returned this:

 

"Seq Scan on my_table  (cost=0.00..4329124.83 rows=1731 width=1700) (actual time=194785.745..1553525.244 rows=138 loops=1)"

"  Filter: (geom && st_geomfromtext('POLYGON ((892267.1937422 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))'::text, st_srid(geom)))"

"  Rows Removed by Filter: 17311187"

"Total runtime: 1553525.352 ms"

 

The POLYGON described above is located at the outer edge of the 17mio geometries and the extent is pretty small.

 

I have executed a VACUUM ANALYZE to clean up the statistics, which didn’t seem to improve the results.

 

My INDEX has been created like this:

 

CREATE INDEX my_table_geom_idx

  ON my_table

  USING gist

  (geom);

 

Upon reading up on this issue I have changed the following in my postgresql.conf:

 

random_page_cost = 2.0

shared_buffers = 512MB

work_mem = 8MB

maintenance_work_mem = 256MB

effective_cache_size = 8GB

 

The HARDWARE is a Windows 2012R2 with 12GB ram and SAS HDDs. PostgreSQL 9.3 x64 and PostGIS 2.1.1.

 

Can any ask me the right questions so I can solve my INDEX problem?

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Roger Edmunds | 14 Dec 21:43 2014
Picon

st_concavehull



I've been using postgis for a while now but am now trying to use st_concavehull for the first time and I'm having some issues. In the end I started running the examples in the documentation and I'm still having difficulties. 

Using the "L" shape example in the documentation I tried a range of different target_percent values in PgAdmin using a query like this:
 "SELECT  st_dumppoints(ST_ConcaveHull(ST_Collect(geom),0.97))FROM l_shape;" . 

It seems that going below a value of 0.98 for the target_percent does not make any difference to the output.
 When running in PgAdmin, with a target_percent of 0.98 or less, as well as the output always being the same, a notice is raised, the first line of which says:
 "NOTICE:  gserialized_gist_joinsel: jointype 1 not supported".

 To check the output I looked at the output with Qgis and it seems there are just three steps in the output.

1) From 1.0 down to 0.99999999999999995 the output is the same as the output from st_convexhull.
2) From 0.99999999999999994 down to 0.98000000000000004 the output is as per the picture in the documentation for a target_percent of 0.99.
3) From 0.98000000000000003 and all values below the output is as per the picture in the documentation for a target_percent of 0.80 and the notice, mentioned above, is displayed in PgAdmin.

Unless I am really misunderstanding st_concavehull this is not the behaviour I would expect. Does anyone have any suggestions as to what I may be doing wrong?

 
I am using:
PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 32-bit
and 
POSTGIS="2.1.2 r12389" 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="UNKNOWN" RASTER

I have tried this on Windows 7 and Ubuntu 14.04LTS with the same results.

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Beekk Meess | 14 Dec 11:48 2014
Picon

Re : postgis-users Digest, Vol 154, Issue 12

may be the solution exist if there is a way to changing the interne schemas(meta data level) of postgis topology
--------------------------------------------
En date de : Sam 13.12.14, postgis-users-request <at> lists.osgeo.org
<postgis-users-request <at> lists.osgeo.org> a écrit :

 Objet: postgis-users Digest, Vol 154, Issue 12
 À: postgis-users <at> lists.osgeo.org
 Date: Samedi 13 décembre 2014, 21h00

 Send postgis-users mailing list
 submissions to
     postgis-users <at> lists.osgeo.org

 To subscribe or unsubscribe via the World Wide Web, visit
     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 or, via email, send a message with subject or body 'help'
 to
     postgis-users-request <at> lists.osgeo.org

 You can reach the person managing the list at
     postgis-users-owner <at> lists.osgeo.org

 When replying, please edit your Subject line so it is more
 specific
 than "Re: Contents of postgis-users digest..."

 
 Today's Topics:

    1. Re: reverse_geocode not returning
 predirabbrev
       (Paragon Corporation)
    2. neeed help in postgis (Beekk Meess)
    3. Re: neeed help in postgis (Beekk
 Meess)
    4. Re: neeed help in postgis (Stephen
 Woodbridge)
    5. Re: neeed help in postgis (Sandro
 Santilli)

 
 ----------------------------------------------------------------------

 Message: 1
 Date: Fri, 12 Dec 2014 15:45:55 -0500
 From: "Paragon Corporation" <lr <at> pcorp.us>
 To: "'PostGIS Users Discussion'" <postgis-users <at> lists.osgeo.org>
 Subject: Re: [postgis-users] reverse_geocode not returning
     predirabbrev
 Message-ID: <5967C58D2B2142C1A0DF9F79635F5C20 <at> O>
 Content-Type: text/plain;   
 charset="us-ascii"

 Barry,

 Sorry for the late reply.  Completely missed this
 email.  This issue was
 fixed in 2.1.4 (as detailed here http://postgis.net/2014/09/10/postgis-2.1.4
 )
 so just upgrade your postgis_tiger_geocoder to the latest.

 
 If that is not possible you can just replace the
 reverse_geocoder function
 you have with the one in this ticket.  Make sure to set
 the path to tiger
 though otherwise will get installed in your default schema.

 http://trac.osgeo.org/postgis/ticket/2927

 Hope that helps,
 Regina
 http://www.postgis.us
 http://postgis.net

 

 

 -- ORIGINAL MESSAGE --
 I have an existing daily utilized PostGIS install on psql
 9.2. It was
 functioning fine up until the latest change. This change was
 the
 installation of the tz_world shapefile from http://efele.net/maps/tz/world/
 to use lon/lat for getting timezone data.

 Since the installation reverse_geocode(); is still
 functional; however, I
 never get data in predirabbrev. Going back through about
 7.9M records that
 have been geocoded, not one has N, W, S or any data that (I
 would assume)
 would come from direction_lookup.

 I've attempted to re run indexes with no luck. I installed
 the tz shapefile
 with: shp2pgsql -S -s 4326 -I tz_world | psql -U postgres
 geocoder

 
 Any help would be greatly appreciated!

 Thanks!

 
 ----------------------------------------------------------------------------
 --------------------------
 Linux ip-172-16-x-x 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22
 03:15:09 UTC
 2013 x86_64 x86_64 x86_64 GNU/Linux
 ---------------------------------------------------
 PostgreSQL "9.2.9"
 ---------------------------------------------------
 SELECT extname, extversion
 FROM pg_extension;
 "plpgsql";"1.0"
 "postgis";"2.1.3"
 "postgis_topology";"2.1.3"
 "fuzzystrmatch";"1.0"
 "postgis_tiger_geocoder";"2.1.3"
 "plsh";"2"
 ---------------------------------------------------
 "POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921"
 PROJ="Rel. 4.8.0, 6
 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08"
 LIBXML="2.7.6"
 LIBJSON="UNKNOWN" TOPOLOGY RASTER"

 

 
 ------------------------------

 Message: 2
 Date: Sat, 13 Dec 2014 14:43:45 +0000
 From: Beekk Meess <marabaut <at> yahoo.fr>
 To: postgis-users <at> lists.osgeo.org
 Subject: [postgis-users] neeed help in postgis
 Message-ID:
     <1418481825.69311.YahooMailBasic <at> web171306.mail.ir2.yahoo.com>
 Content-Type: text/plain; charset=utf-8

 hello, 
 and when i add an edge for example, all  topology
 relations are changing by adding an edge. i want to adding
 an edge and controlling the changint of topology. i mean i
 want to make more then one network edges or faces topology
 in the same topologic schema. in reality for example if i
 have a bridge above an road. edges representing way in the
 under level make a topology and edges connecting with the
 bridge making other network topology but all these edges in
 the same table. i'm sorry for my bad english again.
    

 
 ------------------------------

 Message: 3
 Date: Sat, 13 Dec 2014 14:45:14 +0000
 From: Beekk Meess <marabaut <at> yahoo.fr>
 To: postgis-users <at> lists.osgeo.org
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID:
     <1418481914.556.YahooMailBasic <at> web171302.mail.ir2.yahoo.com>
 Content-Type: text/plain; charset=iso-8859-1

 hello, i m very happy to your request, thank your very much,
 but i want to use the topology but there is a lot of
 instructions like ST_AddEdgeModFace
 and when i add an edge for example, all  topology
 relations are changing by adding an edge. i want to adding
 an edge and controlling the changint of topology. i mean i
 want to make more then one network edges or faces topology
 in the same topologic schema. in reality for example if i
 have a bridge above an road. edges representing way in the
 under level make a topology and edges connecting with the
 bridge making other network topology but all these edges in
 the same table. i'm sorry for my bad english
 again.   
 --------------------------------------------
 En date de?: Sam 13.12.14, Sandro Santilli <strk <at> keybit.net> a
 ?crit?:

  Objet: Re: neeed help in postgis
  ?: "Beekk Meess" <marabaut <at> yahoo.fr>
  Date: Samedi 13 d?cembre 2014, 14h27

  On Sat, Dec 13, 2014 at
  03:00:07AM +0000, Beekk Meess wrote:
  >
  hello, i m very happy to your request, thank your very
  much
  > but i want to use the topology but
  there is a lot of instructions like ST_AddEdgeModFace
  [...]

  But
  you're still writing to me privately, while I asked you
  to use
  the mailing list :)

  --strk;

   ()?
  ASCII ribbon campaign? --? Keep it simple !
   /\? http://strk.keybit.net/rants/ascii_mails.txt
   

 

 ------------------------------

 Message: 4
 Date: Sat, 13 Dec 2014 10:50:21 -0500
 From: Stephen Woodbridge <woodbri <at> swoodbridge.com>
 To: postgis-users <at> lists.osgeo.org
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID: <548C603D.2050808 <at> swoodbridge.com>
 Content-Type: text/plain; charset=windows-1252;
 format=flowed

 Hi Beekk,

 I can't offer help with the postgis topology, but in
 pgrouting we do 
 this with a zlevel attribute at each node. So the water
 might have 
 zlevel=1, the bridge would have zlevel=2 and if you had a
 tunnel going 
 under the river it might have zlevel=0. Then when we build
 our topology 
 for routing (NOT the same thing as postgis topology), we use
 the zlevel 
 to separate nodes at different levels.

 I'm not sure if postgis topology has a similar mechanism.

 -Steve

 On 12/13/2014 9:45 AM, Beekk Meess wrote:
 > hello, i m very happy to your request, thank your very
 much, but i
 > want to use the topology but there is a lot of
 instructions like
 > ST_AddEdgeModFace and when i add an edge for example,
 all  topology
 > relations are changing by adding an edge. i want to
 adding an edge
 > and controlling the changint of topology. i mean i want
 to make more
 > then one network edges or faces topology in the same
 topologic
 > schema. in reality for example if i have a bridge above
 an road.
 > edges representing way in the under level make a
 topology and edges
 > connecting with the bridge making other network
 topology but all
 > these edges in the same table. i'm sorry for my bad
 english again.
 > -------------------------------------------- En date de
 : Sam
 > 13.12.14, Sandro Santilli <strk <at> keybit.net> a
 ?crit :
 >
 > Objet: Re: neeed help in postgis ?: "Beekk Meess"
 > <marabaut <at> yahoo.fr>
 Date: Samedi 13 d?cembre 2014, 14h27
 >
 > On Sat, Dec 13, 2014 at 03:00:07AM +0000, Beekk Meess
 wrote:
 >>
 > hello, i m very happy to your request, thank your very
 much
 >> but i want to use the topology but
 > there is a lot of instructions like ST_AddEdgeModFace
 [...]
 >
 > But you're still writing to me privately, while I asked
 you to use
 > the mailing list :)
 >
 > --strk;
 >
 > () ASCII ribbon campaign  --  Keep it simple
 ! /\
 > http://strk.keybit.net/rants/ascii_mails.txt
 >
 >
 > _______________________________________________
 postgis-users mailing
 > list postgis-users <at> lists.osgeo.org
 > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 >

 

 ------------------------------

 Message: 5
 Date: Sat, 13 Dec 2014 18:10:26 +0100
 From: Sandro Santilli <strk <at> keybit.net>
 To: PostGIS Users Discussion <postgis-users <at> lists.osgeo.org>
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID: <20141213171026.GF31398 <at> localhost>
 Content-Type: text/plain; charset=us-ascii

 On Sat, Dec 13, 2014 at 10:50:21AM -0500, Stephen Woodbridge
 wrote:
 > Hi Beekk,
 > 
 > I can't offer help with the postgis topology, but in
 pgrouting we do
 > this with a zlevel attribute at each node. So the water
 might have
 > zlevel=1, the bridge would have zlevel=2 and if you had
 a tunnel
 > going under the river it might have zlevel=0. Then when
 we build our
 > topology for routing (NOT the same thing as postgis
 topology), we
 > use the zlevel to separate nodes at different levels.
 > 
 > I'm not sure if postgis topology has a similar
 mechanism.

 No way to avoid a node on intersection with postgis
 topology
 (and ISO Geometry Topology, differently from ISO Network
 Topology)

 --strk;

 
 ------------------------------

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

 End of postgis-users Digest, Vol 154, Issue 12
 **********************************************
 
Beekk Meess | 14 Dec 11:46 2014
Picon

Re : postgis-users Digest, Vol 154, Issue 12

thanks, yes of course. 
But, is there any way to do the same with polygone in postgis Topology? I nedd to do the same think in topology
postgis. so i have a topology shemas in every level and i want to use them in the same schemas. if i changing
the zlevel of an edge or polygone the topology are changing automatically according to the new zlevel. is
there any one can help ???
--------------------------------------------
En date de : Sam 13.12.14, postgis-users-request <at> lists.osgeo.org
<postgis-users-request <at> lists.osgeo.org> a écrit :

 Objet: postgis-users Digest, Vol 154, Issue 12
 À: postgis-users <at> lists.osgeo.org
 Date: Samedi 13 décembre 2014, 21h00

 Send postgis-users mailing list
 submissions to
     postgis-users <at> lists.osgeo.org

 To subscribe or unsubscribe via the World Wide Web, visit
     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 or, via email, send a message with subject or body 'help'
 to
     postgis-users-request <at> lists.osgeo.org

 You can reach the person managing the list at
     postgis-users-owner <at> lists.osgeo.org

 When replying, please edit your Subject line so it is more
 specific
 than "Re: Contents of postgis-users digest..."

 
 Today's Topics:

    1. Re: reverse_geocode not returning
 predirabbrev
       (Paragon Corporation)
    2. neeed help in postgis (Beekk Meess)
    3. Re: neeed help in postgis (Beekk
 Meess)
    4. Re: neeed help in postgis (Stephen
 Woodbridge)
    5. Re: neeed help in postgis (Sandro
 Santilli)

 
 ----------------------------------------------------------------------

 Message: 1
 Date: Fri, 12 Dec 2014 15:45:55 -0500
 From: "Paragon Corporation" <lr <at> pcorp.us>
 To: "'PostGIS Users Discussion'" <postgis-users <at> lists.osgeo.org>
 Subject: Re: [postgis-users] reverse_geocode not returning
     predirabbrev
 Message-ID: <5967C58D2B2142C1A0DF9F79635F5C20 <at> O>
 Content-Type: text/plain;   
 charset="us-ascii"

 Barry,

 Sorry for the late reply.  Completely missed this
 email.  This issue was
 fixed in 2.1.4 (as detailed here http://postgis.net/2014/09/10/postgis-2.1.4
 )
 so just upgrade your postgis_tiger_geocoder to the latest.

 
 If that is not possible you can just replace the
 reverse_geocoder function
 you have with the one in this ticket.  Make sure to set
 the path to tiger
 though otherwise will get installed in your default schema.

 http://trac.osgeo.org/postgis/ticket/2927

 Hope that helps,
 Regina
 http://www.postgis.us
 http://postgis.net

 

 

 -- ORIGINAL MESSAGE --
 I have an existing daily utilized PostGIS install on psql
 9.2. It was
 functioning fine up until the latest change. This change was
 the
 installation of the tz_world shapefile from http://efele.net/maps/tz/world/
 to use lon/lat for getting timezone data.

 Since the installation reverse_geocode(); is still
 functional; however, I
 never get data in predirabbrev. Going back through about
 7.9M records that
 have been geocoded, not one has N, W, S or any data that (I
 would assume)
 would come from direction_lookup.

 I've attempted to re run indexes with no luck. I installed
 the tz shapefile
 with: shp2pgsql -S -s 4326 -I tz_world | psql -U postgres
 geocoder

 
 Any help would be greatly appreciated!

 Thanks!

 
 ----------------------------------------------------------------------------
 --------------------------
 Linux ip-172-16-x-x 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22
 03:15:09 UTC
 2013 x86_64 x86_64 x86_64 GNU/Linux
 ---------------------------------------------------
 PostgreSQL "9.2.9"
 ---------------------------------------------------
 SELECT extname, extversion
 FROM pg_extension;
 "plpgsql";"1.0"
 "postgis";"2.1.3"
 "postgis_topology";"2.1.3"
 "fuzzystrmatch";"1.0"
 "postgis_tiger_geocoder";"2.1.3"
 "plsh";"2"
 ---------------------------------------------------
 "POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921"
 PROJ="Rel. 4.8.0, 6
 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08"
 LIBXML="2.7.6"
 LIBJSON="UNKNOWN" TOPOLOGY RASTER"

 

 
 ------------------------------

 Message: 2
 Date: Sat, 13 Dec 2014 14:43:45 +0000
 From: Beekk Meess <marabaut <at> yahoo.fr>
 To: postgis-users <at> lists.osgeo.org
 Subject: [postgis-users] neeed help in postgis
 Message-ID:
     <1418481825.69311.YahooMailBasic <at> web171306.mail.ir2.yahoo.com>
 Content-Type: text/plain; charset=utf-8

 hello, 
 and when i add an edge for example, all  topology
 relations are changing by adding an edge. i want to adding
 an edge and controlling the changint of topology. i mean i
 want to make more then one network edges or faces topology
 in the same topologic schema. in reality for example if i
 have a bridge above an road. edges representing way in the
 under level make a topology and edges connecting with the
 bridge making other network topology but all these edges in
 the same table. i'm sorry for my bad english again.
    

 
 ------------------------------

 Message: 3
 Date: Sat, 13 Dec 2014 14:45:14 +0000
 From: Beekk Meess <marabaut <at> yahoo.fr>
 To: postgis-users <at> lists.osgeo.org
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID:
     <1418481914.556.YahooMailBasic <at> web171302.mail.ir2.yahoo.com>
 Content-Type: text/plain; charset=iso-8859-1

 hello, i m very happy to your request, thank your very much,
 but i want to use the topology but there is a lot of
 instructions like ST_AddEdgeModFace
 and when i add an edge for example, all  topology
 relations are changing by adding an edge. i want to adding
 an edge and controlling the changint of topology. i mean i
 want to make more then one network edges or faces topology
 in the same topologic schema. in reality for example if i
 have a bridge above an road. edges representing way in the
 under level make a topology and edges connecting with the
 bridge making other network topology but all these edges in
 the same table. i'm sorry for my bad english
 again.   
 --------------------------------------------
 En date de?: Sam 13.12.14, Sandro Santilli <strk <at> keybit.net> a
 ?crit?:

  Objet: Re: neeed help in postgis
  ?: "Beekk Meess" <marabaut <at> yahoo.fr>
  Date: Samedi 13 d?cembre 2014, 14h27

  On Sat, Dec 13, 2014 at
  03:00:07AM +0000, Beekk Meess wrote:
  >
  hello, i m very happy to your request, thank your very
  much
  > but i want to use the topology but
  there is a lot of instructions like ST_AddEdgeModFace
  [...]

  But
  you're still writing to me privately, while I asked you
  to use
  the mailing list :)

  --strk;

   ()?
  ASCII ribbon campaign? --? Keep it simple !
   /\? http://strk.keybit.net/rants/ascii_mails.txt
   

 

 ------------------------------

 Message: 4
 Date: Sat, 13 Dec 2014 10:50:21 -0500
 From: Stephen Woodbridge <woodbri <at> swoodbridge.com>
 To: postgis-users <at> lists.osgeo.org
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID: <548C603D.2050808 <at> swoodbridge.com>
 Content-Type: text/plain; charset=windows-1252;
 format=flowed

 Hi Beekk,

 I can't offer help with the postgis topology, but in
 pgrouting we do 
 this with a zlevel attribute at each node. So the water
 might have 
 zlevel=1, the bridge would have zlevel=2 and if you had a
 tunnel going 
 under the river it might have zlevel=0. Then when we build
 our topology 
 for routing (NOT the same thing as postgis topology), we use
 the zlevel 
 to separate nodes at different levels.

 I'm not sure if postgis topology has a similar mechanism.

 -Steve

 On 12/13/2014 9:45 AM, Beekk Meess wrote:
 > hello, i m very happy to your request, thank your very
 much, but i
 > want to use the topology but there is a lot of
 instructions like
 > ST_AddEdgeModFace and when i add an edge for example,
 all  topology
 > relations are changing by adding an edge. i want to
 adding an edge
 > and controlling the changint of topology. i mean i want
 to make more
 > then one network edges or faces topology in the same
 topologic
 > schema. in reality for example if i have a bridge above
 an road.
 > edges representing way in the under level make a
 topology and edges
 > connecting with the bridge making other network
 topology but all
 > these edges in the same table. i'm sorry for my bad
 english again.
 > -------------------------------------------- En date de
 : Sam
 > 13.12.14, Sandro Santilli <strk <at> keybit.net> a
 ?crit :
 >
 > Objet: Re: neeed help in postgis ?: "Beekk Meess"
 > <marabaut <at> yahoo.fr>
 Date: Samedi 13 d?cembre 2014, 14h27
 >
 > On Sat, Dec 13, 2014 at 03:00:07AM +0000, Beekk Meess
 wrote:
 >>
 > hello, i m very happy to your request, thank your very
 much
 >> but i want to use the topology but
 > there is a lot of instructions like ST_AddEdgeModFace
 [...]
 >
 > But you're still writing to me privately, while I asked
 you to use
 > the mailing list :)
 >
 > --strk;
 >
 > () ASCII ribbon campaign  --  Keep it simple
 ! /\
 > http://strk.keybit.net/rants/ascii_mails.txt
 >
 >
 > _______________________________________________
 postgis-users mailing
 > list postgis-users <at> lists.osgeo.org
 > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 >

 

 ------------------------------

 Message: 5
 Date: Sat, 13 Dec 2014 18:10:26 +0100
 From: Sandro Santilli <strk <at> keybit.net>
 To: PostGIS Users Discussion <postgis-users <at> lists.osgeo.org>
 Subject: Re: [postgis-users] neeed help in postgis
 Message-ID: <20141213171026.GF31398 <at> localhost>
 Content-Type: text/plain; charset=us-ascii

 On Sat, Dec 13, 2014 at 10:50:21AM -0500, Stephen Woodbridge
 wrote:
 > Hi Beekk,
 > 
 > I can't offer help with the postgis topology, but in
 pgrouting we do
 > this with a zlevel attribute at each node. So the water
 might have
 > zlevel=1, the bridge would have zlevel=2 and if you had
 a tunnel
 > going under the river it might have zlevel=0. Then when
 we build our
 > topology for routing (NOT the same thing as postgis
 topology), we
 > use the zlevel to separate nodes at different levels.
 > 
 > I'm not sure if postgis topology has a similar
 mechanism.

 No way to avoid a node on intersection with postgis
 topology
 (and ISO Geometry Topology, differently from ISO Network
 Topology)

 --strk;

 
 ------------------------------

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

 End of postgis-users Digest, Vol 154, Issue 12
 **********************************************
 
Beekk Meess | 13 Dec 15:45 2014
Picon

Re: neeed help in postgis

hello, i m very happy to your request, thank your very much, but i want to use the topology but there is a lot of
instructions like ST_AddEdgeModFace
and when i add an edge for example, all  topology relations are changing by adding an edge. i want to adding an
edge and controlling the changint of topology. i mean i want to make more then one network edges or faces
topology in the same topologic schema. in reality for example if i have a bridge above an road. edges
representing way in the under level make a topology and edges connecting with the bridge making other
network topology but all these edges in the same table. i'm sorry for my bad english again.   
--------------------------------------------
En date de : Sam 13.12.14, Sandro Santilli <strk <at> keybit.net> a écrit :

 Objet: Re: neeed help in postgis
 À: "Beekk Meess" <marabaut <at> yahoo.fr>
 Date: Samedi 13 décembre 2014, 14h27

 On Sat, Dec 13, 2014 at
 03:00:07AM +0000, Beekk Meess wrote:
 >
 hello, i m very happy to your request, thank your very
 much
 > but i want to use the topology but
 there is a lot of instructions like ST_AddEdgeModFace
 [...]

 But
 you're still writing to me privately, while I asked you
 to use
 the mailing list :)

 --strk;

  () 
 ASCII ribbon campaign  --  Keep it simple !
  /\  http://strk.keybit.net/rants/ascii_mails.txt

 
Beekk Meess | 13 Dec 15:43 2014
Picon

neeed help in postgis

hello, 
and when i add an edge for example, all  topology relations are changing by adding an edge. i want to adding an
edge and controlling the changint of topology. i mean i want to make more then one network edges or faces
topology in the same topologic schema. in reality for example if i have a bridge above an road. edges
representing way in the under level make a topology and edges connecting with the bridge making other
network topology but all these edges in the same table. i'm sorry for my bad english again.
Guillaume Drolet | 10 Dec 15:41 2014
Picon

Dynamic parameters to ST_MapAlgebra

Hi,

I'm trying to find the best way to dynamically apply scaling factors to a
raster. I've tried different approaches using ST_MapAlgebra, both callback
and expression versions. The fastest method so far is this one but it
doesn't allow for dynamic parameters:

    SELECT ST_MapAlgebra(rast, 1, '32BF', '([rast] * 0.01) + 0') AS rast,
    FROM evi;

Compared to using the following function, the query above is ten times
faster:

    CREATE OR REPLACE FUNCTION public.scale_sds_plv8(value double
precision[][][], 
	pos integer[][], VARIADIC userargs text[])
    RETURNS double precision AS
    $$
      var g = Number(userargs[0]);
      var o = Number(userargs[1]);

      return (value * g + o);

    $$ LANGUAGE plv8 IMMUTABLE;

    WITH scaling_params AS (
	SELECT gain, off_set
	FROM meta
	WHERE product = 'MCD12Q2' AND sds = 'EVI'
    ) 
    SELECT ST_MapAlgebra(rast, 1, 'scale_sds_plv8(double
            precision[], integer[], text[])'::regprocedure, '32BF', 'FIRST',
NULL::raster, 0, 0, 
            VARIADIC ARRAY[gain, off_set]::text[]) AS rast 
    FROM evi, scaling_params 

I want to take advantage of the speed of the first method above but be able
to pass parameters dynamically instead of hard-coding them in the query. To
this aim I tried this approach:

    CREATE OR REPLACE FUNCTION scale_sds_plpgsql(rast raster, gain float8,
offs float8 DEFAULT 0)
    RETURNS raster AS $$
    DECLARE
        sql text;
    BEGIN
        sql := 'SELECT ST_MapAlgebra(' || rast || 
            ', 1, ''32BF'', ''([rast] * ' ||
            gain || ') + ' || offs || ''')';

        EXECUTE sql;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE;

    SELECT scale_sds_plpgsql(rast, 0.01, 0.0)
    FROM evi;

Running this last query, I get a synthax error which I don't understand:  

ERREUR: erreur de syntaxe sur ou près de «
F400000000000407FC05E10B2668C203F41A0BD86AA1F7C2....
CONTEXT:  fonction PL/pgsql scale_rast(raster,double precision,double
precision), ligne 12 à instruction EXECUTE
********** Error **********

I'm sure there are experienced folks on this list who will have some clues
to solve this or who will come up with a better approach. 

Thanks a lot for your time.

G

--
View this message in context: http://postgis.17.x6.nabble.com/Dynamic-parameters-to-ST-MapAlgebra-tp5007468.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
Barry McCall | 9 Dec 19:54 2014
Picon

reverse_geocode not returning predirabbrev

I have an existing daily utilized PostGIS install on psql 9.2. It was functioning fine up until the latest change. This change was the installation of the tz_world shapefile from http://efele.net/maps/tz/world/ to use lon/lat for getting timezone data.

Since the installation reverse_geocode(); is still functional; however, I never get data in predirabbrev. Going back through about 7.9M records that have been geocoded, not one has N, W, S or any data that (I would assume) would come from direction_lookup.

I've attempted to re run indexes with no luck. I installed the tz shapefile with: shp2pgsql -S -s 4326 -I tz_world | psql -U postgres geocoder


Any help would be greatly appreciated!

Thanks!

------------------------------------------------------------------------------------------------------
Linux ip-172-16-x-x 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
---------------------------------------------------
PostgreSQL "9.2.9"
---------------------------------------------------
SELECT extname, extversion
FROM pg_extension;
"plpgsql";"1.0"
"postgis";"2.1.3"
"postgis_topology";"2.1.3"
"fuzzystrmatch";"1.0"
"postgis_tiger_geocoder";"2.1.3"
"plsh";"2"
---------------------------------------------------
"POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY RASTER"
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Alessio Degioannini | 6 Dec 13:32 2014

Help CONSTRAINTS and others questions

Hi,
I'm a postgis/postgres newbie and I I would like to get some help on the topics that I'm going to describe.

I have imported into the database the shapefile "survey" of points (trees). The spatial table "survey" contains five columns:
- "Gid" (created during import and primary key, integer)
- "Id"
- "Code"
- "Old number"
- "Geom" (it also created during import).

The database includes several tables ("Species", "Defects", "Disease" and others) related to the main table "VTA".
Now I would like to know why, unlike what happened to the other tables, I can not connect with "ADD CONSTRAINT" column "code" (integer, not null, unique) of the table "VTA" to the corresponding column "code" (also integer, not null) the spatial table "survey".

Another question: when I'm editing a field (for example "species name" in the column "species" of table "VTA", related to the table "Species", would that "species name" could be completed on the basis of the records in the table "Species".
How can I do this?
Thanks in advance.

Alessio



_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Rhys A.D. Stewart | 6 Dec 12:42 2014
Picon

postgis.gdal_enabled_drivers not working out for me

Greetings all,

Tried using st_aspng and got a lovely error saying

   ERROR:  rt_raster_to_gdal: Could not load the output GDAL driver

Did some research and found out about postgis.gdal_enabled_drivers. I
set it to 'ENABLE_ALL'  in postgres.conf and no luck. Tried as a
session variable but still no luck.

I can't figure out what I am missing.

Here is my version info:
POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,
6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.9.2"
LIBJSON="UNKNOWN" RASTER

PostgreSQL 9.3.5 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Funtoo 4.8.2-r3) 4.8.2, 64-bit

Rhys
Peace & Love|Live Long & Prosper
Burgess, Freddie | 5 Dec 23:46 2014

Re: PostgreSQL 9.3.4/PostGIS 2.1.1 function

Will this function work?

Does this look right

--
-- Name: polygon_radiant_to_latLong(geometry); Type: FUNCTION; Schema: public; Owner: -
--

CREATE OR REPLACE FUNCTION polygon_radian_to_latLong(geom geometry)
returns geometry[] as
$$
select array_agg(latlong) from (
select path,st_astext(geom) as "radians",st_setsrid(st_scale(st_astext(geom)::geometry,180.0/pi(),180.0/pi()),4326) as "latlong"
  from ST_DumpPoints($1::geometry) as p) as foo;
$$ LANGUAGE 'sql';

ownsdb=# select * from (
ownsdb(# select path,st_astext(geom) as "radians",st_astext(st_setsrid(st_scale(st_astext(geom)::geometry,180.0/pi(),180.0/pi()),4326)) as "latlong"
ownsdb(#   from ST_DumpPoints('POLYGON((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7))'::geometry) as p) as foo;
 path  |          radians          |                 latlong                
-------+---------------------------+------------------------------------------
 {1,1} | POINT(326454.7 5455793.7) | POINT(18704476.5122094 312593952.904064)
 {1,2} | POINT(326621.3 5455813.7) | POINT(18714021.9890763 312595098.819654)
 {1,3} | POINT(326455.4 5455796.6) | POINT(18704516.6192551 312594119.061824)
 {1,4} | POINT(326454.7 5455793.7) | POINT(18704476.5122094 312593952.904064)
(4 rows)


select st_astext(unnest(polygon_radiant_to_latLong('POLYGON((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7))'::geometry)));

thanks

From: Burgess, Freddie
Sent: Friday, December 05, 2014 5:40 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] PostgreSQL 9.3.4/PostGIS 2.1.1 function

From: postgis-users-bounces <at> lists.osgeo.org [postgis-users-bounces <at> lists.osgeo.org] on behalf of Stephen Mather [stephen <at> smathermather.com]
Sent: Friday, December 05, 2014 4:34 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PostgreSQL 9.3.4/PostGIS 2.1.1 function

Sorry typos and such, try this again:

select st_astext(st_setsrid(st_scale(geom,180.0/pi(),180.0/pi()),4326)) FROM table_containing_polygons;

Best,
Steve

On Fri, Dec 5, 2014 at 4:30 PM, Stephen Mather <stephen <at> smathermather.com> wrote:
Hi Freddie,

select st_astext(st_setsrid(st_scale('POINT(1
1)'::geometry,180.0/pi(),180.0/pi()),4326)) FROM table_containing_polygon;

Best,
Steve


On Fri, Dec 5, 2014 at 3:33 PM, Burgess, Freddie <FBurgess <at> radiantblue.com> wrote:
How would I do this on a polygon?

Thanks
________________________________________
From: postgis-users-bounces <at> lists.osgeo.org [postgis-users-bounces <at> lists.osgeo.org] on behalf of Paul Ramsey [pramsey <at> cleverelephant.ca]
Sent: Thursday, December 04, 2014 6:51 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PostgreSQL 9.3.4/PostGIS 2.1.1 function

I would think that scaling by 180/pi should do the trick in general?

select st_astext(st_setsrid(st_scale('POINT(1 1)'::geometry,180.0/pi(),180.0/pi()),4326));

P

On Thu, Dec 4, 2014 at 3:24 PM, Burgess, Freddie
<FBurgess <at> radiantblue.com> wrote:
> Given a polygon geometry with no SRID, the vertices actually in radians, but
> since the SRID is zero, it looks like Cartesian coordinates, I want the lat
> long in degrees and not rads.
>
> Does anyone provide of a function/custom that will return lat/long in
> degrees given the conditions described?
>
> thanks
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


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

Gmane