Marc-André Goderre | 27 May 17:32 2015
Picon

Best projection srid for topology

Hello all,
I'm working on a many million lines topology project with OSM data in 4326 srid. I'd like to know if i should
transform their geom in a meter srid ( like 3857). Should it be faster for the 'toTopoGeom()' function to
work with geometry in 4326 or in 3857 srid?
Thanks

Marc
Gabriel Vatin | 27 May 14:57 2015
Picon

Using Postgis raster for water elevation

Hello all,

I'd like to have some feedbacks on an operation that should be possible 
with Postgis raster, but I can't find out how to do this.
I have a whole raster of French DEM (elevation data) stored on PostGis, 
with tiled objects. That makes a table with some 14.000 rows of data: 
france_mnt (rid, rast, filename)
With ST_Value(france_mnt.rast, geometry), I can retrieve the elevation 
of a given coordinates in the geometry object.

I'd like to create a new raster, on the fly, with a maximum value of 
elevation, such as : all pixels with value < 10
This could be used for mapping water elevation in a area, for instance.

Anyone has already done this? I'll be happy to have some tips on the 
function to use.
Thanks!

Gabriel
Alexander W. Rolek | 26 May 22:01 2015
Picon

Slow ST_Intersects and Materialized Views

I have two tables both which have a gemo_4326 columns with a GIST index.
  • gis.parcels (approximately 1 million records)
  • gis.layers (approximately 1 thousand records)
The gis.layers table has large multipolygons that can include thousands of gis.parcel intersects. When I run ST_Intersects from a gis.parcel row to gis.layers the query is pretty quick (sub 100 ms). When I run an ST_Intersects on a large multipolygon from gis.layers to gis.parcels to find which parcels intersect with the gis.layer, the queries can take upwards of 10 minutes.

Here's my query:

SELECT DISTINCT
parcel.apn
FROM 
gis.parcels as parcel, 
gis.layers as layer 
WHERE 
layer.id = 339 AND 
ST_Intersects(layer.geom_4326, parcel.geom_4326);

Two questions:
  • How can I improve performance on the ST_Intersects from gis.layers -> gis.parcels?
  • I'm considering caching the results in a Materialized View, but based on the current performance this would take a couple days. Is there an alternative approach for caching the results?

--
Alexander W. Rolek

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

​ST_MapAlgebra question

hello


​​
ST_MapAlgebra(rast, 1, '32BF', '[rast] / 100.', -9999)

​in this exp.  What does < [rast] / 100. > mean ?
Why do we need to divide the raster value to 100?

I will appreciate if you explain 

regards




--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu


________________________

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
Ahmet Temiz | 26 May 08:47 2015
Picon

My slope calculation is too slow

hello

My slope calculation is very slow.
Can you advice me any better way to speed up slope calculation.

here is the way I have used. 
-------
Hibernate: 
     CREATE VIEW vslp8 AS 
    WITH r AS (  select
         ST_Transform(ST_Union(dem.rast),  32635) AS rast   
     FROM
         rdem2 as dem  
     where
         st_Intersects( st_transform(dem.rast, 32635) , ST_Transform(  ST_GeomFromText('POLYGON((28.87774  40.44479446,28.87774  40.755545,29.4603534  40.755545,29.4603534  40.44479446,28.87774  40.44479446))',4326 ),32635))),

cx AS (SELECT
         ST_AsRaster( sf.the_geom,  r.rast) AS rast 
     FROM
         ( SELECT
             ST_Transform(  ST_GeomFromText('POLYGON((28.87774  40.44479446,28.87774  40.755545,29.4603534  40.755545,29.4603534  40.44479446,28.87774  40.44479446))',
             4326 ),
             32635) AS the_geom ) sf CROSS 
     JOIN r ) 
 SELECT
             ST_Clip(ST_Slope(r.rast, 1,  cx.rast),
             ST_Transform( ST_GeomFromText('POLYGON((28.87774  40.44479446,28.87774  40.755545,29.4603534  40.755545,29.4603534  40.44479446,28.87774  40.44479446))',
             4326 ),  32635)) AS rast  
         FROM
             r  CROSS 
         JOIN cx 

-----

I will appreciate if you propose any way.

regards

--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu


________________________

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
Rushforth, Peter | 25 May 22:59 2015
Picon

coordinate order of geometry vs coordinate order of gml

Hi,

I am confused by coordinate order issues that I noticed when using qgis for the first time today.

I have a table with a geometry column that stores different shapes - points, lines and polygons.

The projection of the column is EPSG:4326 -> 

"(f_table_catalog)";"(f_table_schema)";"(f_table_name)entrystore";"(f_geometry_column)georss_where";(coord_dimension)2;(srid)4326;"(type)GEOMETRY"

When I access the column using qgis, and apparently with pgsql2shp, the coordinate order of the geometry
column is reversed / incorrect. 

When I access the column with ST_AsGML, it is correct.  When I access with ST_AsKML, it reverses the
coordinate order:

select ST_AsKML(georss_where), ST_AsGML(georss_where) from entrystore where updatetimestamp = 914103->
"<Polygon><outerBoundaryIs><LinearRing><coordinates>46,-76 45,-76 45,-74 46,-74
46,-76</coordinates></LinearRing></outerBoundaryIs></Polygon>";"<gml:Polygon
srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>46,-76 45,-76
45,-74 46,-74 46,-76</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>"

Am I wrong in thinking that either ST_AsGML or ST_AsKML has got something wrong?  I would have expected that
PostGIS, knowing the geometry is EPSG:4326,
would return the KML in lon,lat order.  

When I take the centroid of the geometry and report it with ST_AsLatLonText it reports longitude and
latitude reversed, when compared with the GML:

select ST_AsLatLonText(ST_centroid(georss_where)), ST_AsGML(ST_Centroid(georss_where)) from
entrystore where updatetimestamp = 914103
"75°0'0.000"S 45°30'0.000"E";"<gml:Point srsName="EPSG:4326"><gml:coordinates>45.5,-75</gml:coordinates></gml:Point>"

Thank you.
Peter Rushforth

P.S.
I noticed that on this documentation page there are two coordinate orders for apparently a similar area in
North America (?) - EPSG:4269, but
perhaps this is unrelated.

http://postgis.net/docs/ST_GeomFromGML.html 
Ahmet Temiz | 24 May 19:16 2015
Picon

recommendations on slope map

hello

I need to have your recommendations.

I want to build slope map.
I have dem as raster.

which one is the better way ?:
. slope map as differen band on dem.
or
. slope map as different color
or
any other way ?

what do you advice ?

kind regards

--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu


________________________

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
Trang Nguyen | 21 May 02:53 2015

Query plan improvement when identical st_intersects filter is repeated twice

Hi,

 

I am tuning query performance on the  following query and am seeing two completely different query plans for the following. The only difference is that the second query repeats the same filter “st_intersects(waypoints, zone_geom)” a second time, which somehow  seems to trigger an index scan on the gist indexed LINESTRING column “waypoints” that doesn’t occur in the first query.

 

I’ve run analyze on the table so DB stats are current.

 

 

View:

REATE OR REPLACE VIEW od1.v_trip_zone AS

 SELECT z.uuid AS zone_id,

    z.name AS zone_name,

    z.namespace AS zone_namespace,

    z.geom AS zone_geom,

        CASE

            WHEN st_intersects(t.startloc, z.geom) AND st_intersects(t.endloc, z.geom) THEN 2

            WHEN st_intersects(t.startloc, z.geom) THEN 0

            WHEN st_intersects(t.endloc, z.geom) THEN 1

            WHEN st_intersects(t.waypoints, z.geom) THEN 3

            ELSE (-1)

        END AS match_cond,

    t.pkey,

    t.trip_id,

    t.startts,

    t.endts,

    t.startloc,

    t.endloc,

    t.probe_id,

    t.provider_id,

    t.movement_type,

    t.mode,

    t.trip_dist_m,

    t.trip_mean_speed_metersph,

    t.trip_max_speed_metersph,

    t.is_start_home,

    t.is_end_home,

    t.waypoints,

    t.createdts

   FROM od1.trip_v1_partitioned t,

    od1.zone z;

 

 

Query 1:

 

select pkey, trip_id, startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints from od1.v_trip_zone  where zone_id in ('kansas_303','kansas_601','kansas_603','kansas_604','kansas_10','kansas_11','kansas_9','kansas_310','kansas_311','kansas_315','kansas_301','kansas_302','kansas_307','kansas_306','kansas_305','kansas_204','kansas_201','kansas_3','kansas_2','kansas_1')

and st_intersects(waypoints, zone_geom)

and startts>=TIMESTAMP '2015-01-16' and endts<TIMESTAMP '2015-01-31'   and startts<TIMESTAMP '2015-01-31'

order by pkey

limit 2

 

Plan:

"Limit  (cost=82.43..1774.34 rows=2 width=1257)"

"  ->  Nested Loop  (cost=82.43..120241818.53 rows=142137 width=1257)"

"        Join Filter: ((t.waypoints && z.geom) AND _st_intersects(t.waypoints, z.geom))"

"        ->  Merge Append  (cost=0.70..7242945.66 rows=21320513 width=1257)"

"              Sort Key: t.pkey"

"              ->  Index Scan using trip_partitioned_v1_pkey on trip_v1_partitioned t  (cost=0.12..8.15 rows=1 width=216)"

"                    Filter: ((startts >= '2015-01-16 00:00:00'::timestamp without time zone) AND (endts < '2015-01-31 00:00:00'::timestamp without time zone) AND (startts < '2015-01-31 00:00:00'::timestamp without time zone))"

"              ->  Index Scan using trip_v1_y2015m01_pkey on trip_v1_y2015m01 t_1  (cost=0.56..6976431.09 rows=21320512 width=1257)"

"                    Filter: ((startts >= '2015-01-16 00:00:00'::timestamp without time zone) AND (endts < '2015-01-31 00:00:00'::timestamp without time zone) AND (startts < '2015-01-31 00:00:00'::timestamp without time zone))"

"        ->  Materialize  (cost=81.73..154.02 rows=20 width=9864)"

"              ->  Bitmap Heap Scan on zone z  (cost=81.73..153.92 rows=20 width=9864)"

"                    Recheck Cond: ((uuid)::text = ANY ('{kansas_303,kansas_601,kansas_603,kansas_604,kansas_10,kansas_11,kansas_9,kansas_310,kansas_311,kansas_315,kansas_301,kansas_302,kansas_307,kansas_306,kansas_305,kansas_204,kansas_201,kansas_3,kansas_ (...)"

"                    ->  Bitmap Index Scan on idx_uuid  (cost=0.00..81.72 rows=20 width=0)"

"                          Index Cond: ((uuid)::text = ANY ('{kansas_303,kansas_601,kansas_603,kansas_604,kansas_10,kansas_11,kansas_9,kansas_310,kansas_311,kansas_315,kansas_301,kansas_302,kansas_307,kansas_306,kansas_305,kansas_204,kansas_201,kansas_3,kan (...)"

 

Query 2 (Much improved):

 

select pkey, trip_id, startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints from od1.v_trip_zone  where zone_id in ('kansas_303','kansas_601','kansas_603','kansas_604','kansas_10','kansas_11','kansas_9','kansas_310','kansas_311','kansas_315','kansas_301','kansas_302','kansas_307','kansas_306','kansas_305','kansas_204','kansas_201','kansas_3','kansas_2','kansas_1')

and st_intersects(waypoints, zone_geom) and st_intersects(waypoints, zone_geom)

and startts>=TIMESTAMP '2015-01-16' and endts<TIMESTAMP '2015-01-31'   and startts<TIMESTAMP '2015-01-31'

order by pkey

limit 2

 

Plan:

"Limit  (cost=333.74..333.75 rows=2 width=1257)"

"  ->  Sort  (cost=333.74..333.86 rows=47 width=1257)"

"        Sort Key: t.pkey"

"        ->  Nested Loop  (cost=81.73..333.27 rows=47 width=1257)"

"              ->  Bitmap Heap Scan on zone z  (cost=81.73..153.92 rows=20 width=9864)"

"                    Recheck Cond: ((uuid)::text = ANY ('{kansas_303,kansas_601,kansas_603,kansas_604,kansas_10,kansas_11,kansas_9,kansas_310,kansas_311,kansas_315,kansas_301,kansas_302,kansas_307,kansas_306,kansas_305,kansas_204,kansas_201,kansas_3,kansas_ (...)"

"                    ->  Bitmap Index Scan on idx_uuid  (cost=0.00..81.72 rows=20 width=0)"

"                          Index Cond: ((uuid)::text = ANY ('{kansas_303,kansas_601,kansas_603,kansas_604,kansas_10,kansas_11,kansas_9,kansas_310,kansas_311,kansas_315,kansas_301,kansas_302,kansas_307,kansas_306,kansas_305,kansas_204,kansas_201,kansas_3,kan (...)"

"              ->  Append  (cost=0.00..8.95 rows=2 width=736)"

"                    ->  Seq Scan on trip_v1_partitioned t  (cost=0.00..0.00 rows=1 width=216)"

"                          Filter: ((startts >= '2015-01-16 00:00:00'::timestamp without time zone) AND (endts < '2015-01-31 00:00:00'::timestamp without time zone) AND (startts < '2015-01-31 00:00:00'::timestamp without time zone) AND (waypoints && z.geom) (...)"

"                    ->  Index Scan using idx_trip_v1_y2015m01_waypoints on trip_v1_y2015m01 t_1  (cost=0.42..8.95 rows=1 width=1257)"

"                          Index Cond: ((waypoints && z.geom) AND (waypoints && z.geom))"

"                          Filter: ((startts >= '2015-01-16 00:00:00'::timestamp without time zone) AND (endts < '2015-01-31 00:00:00'::timestamp without time zone) AND (startts < '2015-01-31 00:00:00'::timestamp without time zone) AND _st_intersects(waypoi (...)"

 

 

Could someone shed some light on the difference in query planner results?


Thanks,
Trang

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
KhunSanAung | 20 May 04:29 2015
Picon

What is the Polygon thinning function in PostGIS?

Hello All,

I'd like to generated line from elongated polygon in PostGIS.
e.g. extracting center of a river polygon, center of the road polygon, etc.

I'd to know what would be equivalence of Polygon Thinning in PostGIS. 

Thank you very much for any hints.

Best regards
--
Have a nice day!
--

Mr. Khun San Aung

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Hermano ... | 20 May 01:18 2015
Picon

Efficient 4D range query

Hello


How one can express a 4D range query in Postgis? I would like to know what 4d points lie within a hypercube. This is my table:

CREATE TABLE myTable ( point geometry, attrib double precision, CONSTRAINT ge_idx PRIMARY KEY (point) );

This is how I inserted the data points:

insert into myTable (point) values (ST_MakePoint(-71.10, 42.31, 12.43, 54.3));
It's trivial to specify a 2D bounding box, but I couldn't find a way to do the same in 4D.
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
António M. Rodrigues | 19 May 12:24 2015
Picon

problem with intersection

Hi all,

I need two intersect two tables with geometry columns.

I get holes in the resulting geometry (clip1) which I cannot explain and must solve.
(white in file <Screenshot from 2015-05-19 10:50:36.png>).

The geometry tables are testes.inputt and testes.targett (which are samples of two larger tables).

In my attempts to solve to problem, I tried to clean any topology problems with st_cleangeometry and st_makevalid.

My code is:

----------
--step1:
alter table testes.inputt rename column the_geom to the_geom_old;
alter table testes.inputt add column the_geom geometry;
update testes.inputt set the_geom = st_cleangeometry(the_geom_old);

alter table testes.targett rename column the_geom to the_geom_old;
alter table testes.targett add column the_geom geometry;
update testes.targett set the_geom = st_cleangeometry(the_geom_old);

----------
--step2:
alter table testes.inputt rename column the_geom to the_geom_old2;
alter table testes.inputt add column the_geom geometry;
update testes.inputt set the_geom = ST_MakeValid(the_geom_old2);

alter table testes.targett rename column the_geom to the_geom_old2;
alter table testes.targett add column the_geom geometry;
update testes.targett set the_geom = st_cleangeometry(the_geom_old2);

----------
--step3:
DROP TABLE if exists testes.clip1;
CREATE TABLE testes.clip1 AS
SELECT    testes.inputt.ikey, testes.targett.tkey, testes.inputt.iarea,
    st_intersection(testes.inputt.the_geom,testes.targett.the_geom) as the_geom
FROM testes.inputt, testes.targett
WHERE  testes.inputt.the_geom && testes.targett.the_geom
    AND st_intersects(testes.inputt.the_geom,testes.targett.the_geom);

screenshots and data can be found here:
https://www.dropbox.com/sh/9h622gj0qrz1cnx/AADHokAeVtnIC__6CAaqBEaia?dl=0

hope I was clear enough.
Thank you in advance.
regards,
António
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane