gmane.comp.gis.postgis

Re: create 10m buffer using input data in EPSG4326

Jo — 2013-05-22T21:21:10

I'm sorry if this is wrong, but there is another function where casting
thegeom to geography helps with that.

ST_Union(ST_Buffer(roads.the_geom::geography , 10))

I'm rather new at all this, so maybe it's bad advice.

Jo

2013/5/22 karsten <karsten< at >terragis.net>

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

Re: create 10m buffer using input data in EPSG4326

Luis Carrasco — 2013-05-22T20:33:50

Hi Karsten,

One way could be using st_transform inside your query and assign a new
sridin meters during
the selection process, but it depends on whether there's a single
projection in meters you could use for all your unprojected dataset.

Transforming 10m to degrees would be the other solution, but it will differ
by angle, so you may have to use a function. On the other hand, if you are
running calculations on the geometry, it's probably recommendable to
project data first. My 2 cents

SELECT 10 AS score, ST_Difference(
 ST_Union(ST_Buffer(st_transform(roads.the_geom, srid_in_meters), 10)),
 ST_Union(ST_Buffer(st_transform(roads.the_geom, srid_in_meters), 0))
 )
AS the_geom
FROM geodata.roads2012_ca roads
GROUP BY score


On Wed, May 22, 2013 at 4:18 PM, karsten <karsten< at >terragis.net> wrote:

create 10m buffer using input data in EPSG4326

karsten — 2013-05-22T20:18:36

Greetings, I am working on creating 10m buffers around roads:
 
SELECT 10 AS score, ST_Difference(
 ST_Union(ST_Buffer(roads.the_geom, 10)),
 ST_Union(ST_Buffer(roads.the_geom, 0))
 )
AS the_geom
FROM geodata.roads2012_ca roads
GROUP BY score
 
So far I have the roads in dd (EPSG 4326) and was wondering how I could pass
the distances in meters into the buffer function (without necessarily
creating a new column with projected geometries). I guess could use some
geography data type function .. but how ?
 
Thanks
Karsten

Karsten Vennemann
Principal

Terra GIS LTD
2119 Boyer Ave E 
Seattle, WA  98112
USA 
 <http://www.terragis.net/> www.terragis.net

Phone ++1 206 905 1711
Fax      ++1 925 905 1711

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

Re: Area of non-intersecting parts of layers

Cedric Duprez — 2013-05-22T13:29:17

That's it! I could reduce the query to a single spatial join using ST_INTERSECTS, with common table expressions:

WITH t1 AS (
            SELECT name1, SUM(ST_Area(geom)) AS surface
            FROM layer1
            GROUP BY name1
)
, t2 AS (
            SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) AS surface
            FROM layer1 l1
            INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)
            GROUP BY 1, 2
)
, t3 AS (
            SELECT l1.name1, SUM(t2.surface) AS surface
            FROM layer1 l1
            INNER JOIN t2 ON l1.name = t2.name
            GROUP BY 1, 2
)
SELECT t1.name1, t1.surface - t3.surface AS gap
FROM t1
INNER JOIN t3 ON t1.name1 = t3.name1
ORDER BY 1, 2;

This is performing because I just keep 1 spatial inner join, using ST_INTERSECTS.

Thanks a lot for your help.

Kind regards,

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

Re: Area of non-intersecting parts of layers

Hugues François — 2013-05-22T11:44:36

Hi,

 

I think you can use st_difference bu you have to be careful because it will return as many rows as l2 geometries intersect l1 ones. Union them should be a solution but I doubt it is the more performance way :

 

With l2_union as (

                Select l1.id, l1.name, st_union(l2.geom) geom

                From l1, l2

                Where st_intersects(l1.geom, l2.geom)

                Group by l1.id, l1.name

)

 

Select l1.id, l1.name, st_area(st_difference(l1.geom, l2_union.geom))

From l1, l2_union

Where l1.id = l2_union.id

 

Hugues.

 

De : postgis-users-bounces< at >lists.osgeo.org [mailto:postgis-users-bounces< at >lists.osgeo.org] De la part de Cedric Duprez
Envoyé : mercredi 22 mai 2013 08:26
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Area of non-intersecting parts of layers

 

Thanks for the answers.

 

Sorry if I did not explain what I wish correctly.

I'm trying to get the area of the difference between l1 and l2 (which is l1 - intersect(l1, l2)).

 

Cedric

 

______

Re: Area of non-intersecting parts of layers

Cedric Duprez — 2013-05-22T06:26:04

Thanks for the answers.

Sorry if I did not explain what I wish correctly.
I'm trying to get the area of the difference between l1 and l2 (which is l1 - intersect(l1, l2)).

Cedric

________________________________
De : postgis-users-bounces< at >lists.osgeo.org [mailto:postgis-users-bounces< at >lists.osgeo.org] De la part de Nicolas Ribot
Envoyé : mardi 21 mai 2013 22:23
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Area of non-intersecting parts of layers

Hi,

I'm too wondering what should be the final result.

To resolve the case where you also want the the areas of layers 1 geometries that do not contain any layer2 geom, you could use UNION to join the 2 queries:

SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha
FROM layer1 l1
INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)
GROUP BY 1, 2

UNION

select l1.name1, '' as name2, sum(st_area(l1.geom)) / 10000
from layer1 l1
where not exists (
            select l2.id<http://l2.id> from layer2 l2 whe

Re: Area of non-intersecting parts of layers

Nicolas Ribot — 2013-05-21T20:22:52

Hi,

I'm too wondering what should be the final result.

To resolve the case where you also want the the areas of layers 1
geometries that do not contain any layer2 geom, you could use UNION to join
the 2 queries:

SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom)))
/ 10000 AS surface_ha
FROM layer1 l1
INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)
GROUP BY 1, 2

UNION

select l1.name1, '' as name2, sum(st_area(l1.geom)) / 10000
from layer1 l1
where not exists (
select l2.id from layer2 l2 where st_contains(l1.geom, l2.geom)
 )
group by 1;

Nicolas


On 21 May 2013 22:11, Hugues François <hugues.francois< at >irstea.fr> wrote:

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

Re: Area of non-intersecting parts of layers

Hugues François — 2013-05-21T20:11:49

Hello,

 

I'm not sure to understand what you are trying to do. Do you want to find the area of polygons from layer 1 which don't contain any polygon from layer 2 or the difference between l1 and l2  (l1 - intersection(l1,l2)) ?

 

Hugues.

 

 

De : postgis-users-bounces< at >lists.osgeo.org [mailto:postgis-users-bounces< at >lists.osgeo.org] De la part de Cedric Duprez
Envoyé : mardi 21 mai 2013 18:55
À : PostGIS Users Discussion
Objet : [postgis-users] Area of non-intersecting parts of layers

 

Hi all,

 

I have 2 layers composed of multipolygons in Postgis 2.0. Each layer contains polygons with a name that is sometime common to several polygons.

When I try to find area of intersections between the 2 layers, no problem with the following query:

SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha

FROM layer1 l1

INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)

GROUP BY 1, 2

ORDER BY 1, 2;

 

OK.

But I also try to get the area of my layer1 (grouped by

Re: Shp2pgsql : Error in fread()

Hugues François — 2013-05-21T19:43:29

Hello, 

Thanks for your answer. It took me some times to reply because I tried to make test with spliting the shape file : it takes quite a long time to work with million of polygons.

If I'm not wrong, the ticket you linked to is about dbf size and has been fixed in 1.5.3.  On my side, shp is effectively more than 2 Go but dbf file is near 1.5 Go and I work with postgis 2.0.1.

However, I tried to select  the first  10.000.000'th rows and then the last ones within arcgis to export data as a new shapefile. Despite there was no error when I exported the data (or when I reopen it), this time I wasn't able to export the second part of the table further than I could import in postgis.

Finally, I don't know where the problem is exactly but I think it's on the arcgis side.

Hugues.


-----Message d'origine-----
De : postgis-users-bounces< at >lists.osgeo.org [mailto:postgis-users-bounces< at >lists.osgeo.org] De la part de Mark Cave-Ayland
Envoyé : mardi 21 mai 2013 00:26
À : PostGIS Users Discussion
Objet : Re: [po

Area of non-intersecting parts of layers

Cedric Duprez — 2013-05-21T16:55:26

Hi all,

I have 2 layers composed of multipolygons in Postgis 2.0. Each layer contains polygons with a name that is sometime common to several polygons.
When I try to find area of intersections between the 2 layers, no problem with the following query:
SELECT l1.name1, l2.name2, SUM(ST_Area(ST_Intersection(l1.geom, l2.geom))) / 10000 AS surface_ha
FROM layer1 l1
INNER JOIN layer2 l2 ON ST_Intersects(l1.geom, l2.geom)
GROUP BY 1, 2
ORDER BY 1, 2;

OK.
But I also try to get the area of my layer1 (grouped by name1) that does not contain elements of my layer2.

I tried with LEFT JOIN, with ST_CONTAINS, without success.

Does anyone have an idea on how to write this query?

Thanks in advance,

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

Re: permission denied for relation geometry_columnson postgis 2.x

Pietro Rossin — 2013-05-21T14:32:27

Ok, problem solved...

GRANT SELECT ON public.geometry_columns TO gisadmin (or whatever user you
like);

Bie


2013/5/21 Luis Carrasco [via PostGIS] <ml-node+s17n5003344h88< at >n6.nabble.com>





--
View this message in context: http://postgis.17.x6.nabble.com/permission-denied-for-relation-geometry-columns-on-postgis-2-x-tp5003338p5003345.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

Re: permission denied for relation geometry_columns on postgis 2.x

Luis Carrasco — 2013-05-21T13:36:53

You are welcome, Pietro

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

Re: permission denied for relation geometry_columns on postgis 2.x

Pietro Rossin — 2013-05-21T13:28:47

No, I didn't
Gisadmin user isn't the owner of the postgis db and I don't like it to be
so (superuser).
It must only  create tables and edit them
And there are users that can only query tables..
I know there are roles, groups and users in postgres but I didn't
understand how to use them efficiently...
By the way, thanks!
pietro


2013/5/21 Luis Carrasco <leca00< at >gmail.com>

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

Re: permission denied for relation geometry_columns on postgis 2.x

Luis Carrasco — 2013-05-21T13:19:01

If I understand your problem correctly, have you tried changing ownership
to the geometry_columns and geometry_columns (and raster_columns) views  to
gisadmin? That's if gisadmin owns the database you are trying to connect to.



















On Tue, May 21, 2013 at 8:13 AM, Pietro Rossin <pierigis< at >gmail.com> wrote:

permission denied for relation geometry_columns onpostgis 2.x

Pietro Rossin — 2013-05-21T12:13:31

Hello all
how can I solve this problem?
With postgres user no problem (of course), but I made a gisadmin user which
I thought I gave enough grants..
geometry_columns with postgis 2.x is not a table but a view that, if I'm not
wrong, is updated by some trigger/function

How can I make it usable/selectable by users?

Thanks
Pietro




--
View this message in context: http://postgis.17.x6.nabble.com/permission-denied-for-relation-geometry-columns-on-postgis-2-x-tp5003338.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

Re: Shp2pgsql : Error in fread()

Mark Cave-Ayland — 2013-05-20T22:25:30


How large are your shapefiles and what version of PostGIS are you using? 
It looks as if you're trying to load a shapefile > 2GB on a platform 
that doesn't support it, so I'd suggest breaking down your data into 
more yet smaller shapefiles so that they stay under this limit.

You may find some information at 
http://trac.osgeo.org/postgis/ticket/393 helpful.


HTH,

Mark.

Shp2pgsql : Error in fread()

Hugues François — 2013-05-20T12:23:24

Hello,

I have a big amount of data to import ins postgis from shapefile (18.000.000 of squares). As usual, I used shp2pgsql to do that with this command line : shp2pgsql -s 2154 -g geom -W LATIN1 -I /home/myhome/shpname myschema.shpname.

But for each of my file (there are four of them) it throw me the same error :
Error in fread() reading object of size 136 at offset 2147483756 from .shp file
Error reading shape object 15790321

In each of the four file the error happens to the same time / object.

Where did I make a mistake and how can I correct it ?

I found this related info : http://trac.osgeo.org/gdal/ticket/4203

But the answer don't really help me to find any solution... Is anyone there having an idea please ?

Hugues.

Re: toTopoGeom take long time

Sandro Santilli — 2013-05-20T07:16:37

After 22 hours I'd rather keep it going, but next time you'd better do
this in chunks, ie:

  UPDATE plochy SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005)
    WHERE gid >= 0 AND gid < 500;
  UPDATE plochy SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005)
    WHERE gid >= 500 AND gid < 1000;
  ...

Doning so would first of all save partial work (which would instead by
completely reverted if a problem occurs) but could also speed things up
by reducing the memory required to rollback the whole operation.

I think the bad performance is due to slow disk operations, there are
a lot of read/writes to database tables during topology building.

--strk;

Re: postgis topology

Sandro Santilli — 2013-05-20T07:12:49

I'm afraid you can call this "normal", are those polygons complex ?
What's the average number of vertexes in those polygons ?
Note that version of PostGIS and GEOS can make a difference.

PS: Could you please file that ticket about MixedCase topology names ?

--strk;

toTopoGeom take long time

Eva Linhartová — 2013-05-19T17:49:05

Hi,
I'm trying to insert geometry data to topology structure. I have 7000
polygons and the procedure take very long time now it's about 22 hours.

UPDATE plochy  SET topo = topology.toTopoGeom(geom, 'dp_topo',3,0.005);

I run the postgis database on my laptop:
procesor: Intel(R) Core(TM) i5-2430M CPU 2.40GHz
RAM: 8GB
type: 64bit

I'm newbie and I tryed it only on my sample data.
Is this time still ok or should I stop the query?

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

Re: postgis topology

Eva Linhartová — 2013-05-19T08:03:05

Hi,
thx from your advice, I dropped topology DP_TOPO2 and create new one called
dp_topo. I didn't get the error, but the query still working (11 hours). I
expected long time for it because it's working with 7000 polygons, but Is
it still normal?

Eva


2013/5/18 Paragon Corporation <lr< at >pcorp.us>

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