Re: [postgis-users] polygons inside polygon

Thank you Nicolas! Från: postgis-users För Nicolas Ribot Skickat: den 1 december 2021 15:10 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] polygons inside polygon Klicka bara på länkar och öppna bilagor om du litar på avsändaren och vet att innehållet är säkert. Hi Paul, I often

Re: [postgis-users] polygons inside polygon

Thanks Nicolas, I’ve added a boolean column (outer_pg) in my original layer (org). Then I tried to populate that column, but it I don’t have the skills… I tried to use your selection: SELECT DISTINCT p1.fid, p2.fid IS null AS "outer_pg" FROM org p1 LEFT JOIN org p2 ON p1.fid <> p2.fid AND

Re: [postgis-users] polygons inside polygon

Hi, Well I don't think so. Some of the inside polygons can have the same border as the most outer polygon with some of the vertices. Some of the polygons can be half inside and half outside, then I would like them both to be a most outer polygon and some polygons has no inside polygons at all =

Re: [postgis-users] polygons inside polygon

Thank you Marcin! I’m not sure if I understand you correct. Here is what I tried to do to get the outer most polygons: Select * from "org" Where "fid" IN( select distinct on (t1.fid) t1.fid from "org" t1,"org" t2 where ST_Area(t1.the_geom)>ST_Area(t2.the_geom) and

[postgis-users] polygons inside polygon

Hi, I have a polygon layer where polygons are inside other polygons (could be polygons in polygons in polygons...). I would like to separate them in 2 different layers, one layer with the outer most polygon and one layer with all polygons that has a surrounding polygon. Is there a smart way to

[postgis-users] parse error -invalid geometry

Hi list, I have installed PostgreSQL/Postgis on a new computer. All my tables (layers) works ok on the old computer, but when I import them in the pgdatabas on the new computor there is a problem when I try to view them in my java-graphical-viewer. I can view the layer in QGIS, no problem.

Re: [postgis-users] breake lines

Hi, I’ve checked: all geometries are of type ST_MultiLineString Kind regards, Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Malm, Paul (Operations AIM) Skickat: den 27 oktober 2020 13:10 Till: postgis-users@lists.osgeo.org Ämne: Re: [postgis-users] breake lines

Re: [postgis-users] breake lines

Thanks, will check! /paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Alexandre Neto Skickat: den 27 oktober 2020 12:40 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] breake lines Hello Paul, It seems that, at least, one geometry in the data table is not

[postgis-users] breake lines

Hi I would like to break lines in intersections of polygons, but without erasing anything. I have used this (but now suddenly, it does not work) : Create table tmpint2 as WITH data AS ( SELECT * FROM public."linesToBeCut" AS t(id, geom) ), cutter AS ( SELECT * FROM "cutterPolygons" AS

[postgis-users] removing objects and at the same time updating others

Hi list, I have a problem with adjacent polygons when converting them to lines, as I would like to only have one line in the boundaries between the former polygons. Now it could be up to 5. I've been thinking of: 1. Creating a thin buffer around the line strings with the featureId from

[postgis-users] remove duplicate line segments

Hi, If someone could help me with this, I would be grateful. I've converted a polygon layer to a line layer. Sometimes the lines segments are a duplicate of another line segment (from an adjacent polygon). I would like to delete the duplicate lines and only store the one of all duplicate line

Re: [postgis-users] sql query

Thanks, why did I not think of that:) > 19 mars 2020 kl. 11:51 skrev Jorge Gustavo Rocha : > > Hi Paul, > > Maybe you can do it in a second step, with a more simple query, since > the polygons overlap. > > Regards, > > Jorge > >> On 19/03/20 10:18, paul.m...@lfv.se wrote: >> HI, >> >> I’m

Re: [postgis-users] sql query

> 19 mars 2020 kl. 11:51 skrev Jorge Gustavo Rocha : > > Hi Paul, > > Maybe you can do it in a second step, with a more simple query, since > the polygons overlap. > > Regards, > > Jorge > >> On 19/03/20 10:18, paul.m...@lfv.se wrote: >> HI, >> >> I’m trying to improve my current function

[postgis-users] sql query

HI, I'm trying to improve my current function to split polygons with lines. I have 2 layers, a polygon layer and a line layer. I Would like to punch out new polygons from a polygon layer using lines in a linelayer. I've tried this: CREATE TABLE split_polys_tmp AS WITH RECURSIVE split AS (

Re: [postgis-users] Help with SQL

Thanks Felix! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Felix Kunde Skickat: den 9 mars 2020 10:33 Till: postgis-users@lists.osgeo.org Kopia: postgis-users@lists.osgeo.org Ämne: Re: [postgis-users] Help with SQL Hi Paul, no need for subqueries here. Do a spatial

Re: [postgis-users] Help with SQL

Thanks Martin! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Martin Davis Skickat: den 9 mars 2020 18:39 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] Help with SQL A slight correction to the SQL (JOINs need an ON condition, which in this case is simply

[postgis-users] Help with SQL

Hi, I have 2 tables: obstacles (point layer) with a column height_m (INTEGER) and a polyobstacles (polygon layer) the point layer has obstacles all over the map and the polygon layer has some polygons containing some of the obstacle points. Now I would like to select the highest obstacle in each

Re: [postgis-users] st_union

For one script I’m buffering lines and joining those buffers, some overlapping. For another script I’m joining polygons that are cutted in sub maps, no overlapping. So there is both variants and both of those were faster with st_buffer. /Paul Från: postgis-users

Re: [postgis-users] ST_CreateTopoGeo

In both topo1- and topo2.face shows a box where Egypt is located, as for all other countries (type of bounding box?). In both topo1- and topo2.edge_data shows all countries as well as Egypt. Egypt is however intersecting an island in topo2. The island is formed like a banana in topo1 (not

Re: [postgis-users] ST_CreateTopoGeo

Hi, I'm not sure of what you mean, Strk. I create a topology (topo1) with all geometries from a polygon layer. I can see the island and the Egypt polygon in the edge_data, they are not intersecting. Then I create a new topology (topo2) with: select topology.ST_CreateTopoGeo('topo2', the_geom)

Re: [postgis-users] st_union

There was no difference between st_union() and st_unaryunion() when merging all adjacent polygons to larger polygons. The st_buffer() method is 3.5 times faster. /Paul -Ursprungligt meddelande- Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Sandro Santilli

Re: [postgis-users] st_union

Thanks, I'll try it to see if it is faster than: CREATE TABLE public." Areas_union " AS select (ST_dump(ST_Buffer(St_Collect(the_geom), 0))).geom as "the_geom" from " Areas"; /Paul -Ursprungligt meddelande- Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Sandro

Re: [postgis-users] st_union

Hi again! I tried this first: CREATE TABLE public."Areas_union" AS select (st_dump(st_union("the_geom"))).geom from "Areas"; It took 168 sec Then I tried this: CREATE TABLE public." Areas_union " AS select (ST_dump(ST_Buffer(St_Collect(the_geom), 0))).geom as "the_geom" from " Areas"; And

Re: [postgis-users] st_union

Thanks Paul and Alexande! /Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Alexandre Neto Skickat: den 3 mars 2020 01:53 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] st_union Uhmmm... So, if there's no join or where clause, the index is not used at all?

[postgis-users] st_union

Hi list, I would like to do this: CREATE TABLE public."Areas_union" AS select (st_dump(st_union("the_geom"))).geom from "Areas"; But it takes such a long time to execute, is there a smarter way to do it? Kind regards, Paul ___ postgis-users mailing

Re: [postgis-users] ST_CreateTopoGeo

Hi again, I can see in the edge data of topo2 that the main polygon of Egypt intersects with an island next to the main polygon. If I delete the island in the original layer before running the SQL sequence the main polygon of Egypt is included in the result layer. Is there any solution of this

Re: [postgis-users] ST_CreateTopoGeo

Thanks, Sandro! It worked with your LOOP! Great! The problem I can see on the result (I'm simplifying country polygons) is that the large polygon of Finland, Germany, Italy and Egypt is missing. I've tried to change the different tolerances (Simplify tolerance and your loop snap-tolerance). If I

Re: [postgis-users] ST_CreateTopoGeo

Hi, Where do you mean I can play with the tolerance? This is what I have done before the ST_createTopoGeo SELECT topology.CreateTopology('topo1', 4326)"; SELECT topology.ST_CreateTopoGeo('topo1', ST_Collect(geom)) from "countries_first"; Btw, I'm intending to simplify later on in my SQL command

[postgis-users] ST_CreateTopoGeo

Hi, I'm running this command: SELECT topology.ST_CreateTopoGeo('topo1',ST_Collect("geom")) from "countries_first"; and are getting this message: ERROR: Corrupted topology: adjacent edges 71 and -72 bind different face (36 and 0) CONTEXT: SQL-sats: "SELECT topology.ST_AddEdgeModFace(atopology,

Re: [postgis-users] help with a SQL Command

Thanks, Alexandre! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Alexandre Neto Skickat: den 14 februari 2020 00:24 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] help with a SQL Command Hi Paul, Your first Select statement, (before the UNION ALL) has no

Re: [postgis-users] help with a SQL Command solved

Sorry I found what was wrong; Drop table if exists cutted; CREATE TABLE "cutted" as WITH blade AS ( SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom) SELECT a.*, (ST_Dump(ST_Split(a.the_geom, b.geom))).geomFROM shifted AS

[postgis-users] help with a SQL Command

Hi list! I have a problem with a sql command, I would be grateful if someone please could tell me what I'm doing wrong. I have a polygon layer where some polygons exceeds the 180/-180 border. I would like to cut the polygons with a line from 180,90 to 180,-90. I've got this far: Drop table if

Re: [postgis-users] magnetic bearing to true bearing

Thanks! 7 feb. 2020 kl. 15:33 skrev James Klassen :  This will calculate the declamation. It would take some C coding to integrate it with PostgreSQL. https://www.ngdc.noaa.gov/geomag/WMM/soft.shtml On Fri, Feb 7, 2020, 08:14 mailto:paul.m...@lfv.se>> wrote: Hi, list! I’m writing a program

[postgis-users] magnetic bearing to true bearing

Hi, list! I'm writing a program reading a file and creating graphical objects. I have a starting point (lat/lon WGS 84), a length (m) and a magnetic bearing (degrees). I would like to automatically convert the magnetic bearing to a true bearing when they occurs in the file I'm reading. Is there

Re: [postgis-users] can not get st_split to work.

Thanks, Regina Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Regina Obe Skickat: den 23 december 2019 20:31 Till: 'PostGIS Users Discussion' Ämne: Re: [postgis-users] can not get st_split to work. I don't see your blade CTE used at all and both a and b are roads

[postgis-users] can not get st_split to work.

Hi , I've created a point-table (point) where I would like to split the lines in table roads. SQL: CREATE TABLE roads_splitted AS WITH blade AS (SELECT ST_Collect(the_geom) AS the_geom FROM point) SELECT fid, (ST_Dump (ST_Split(

Re: [postgis-users] Lines crossing longitude 180 -180

Thanks for all responses! //Paul Från: mark wynter [mailto:m...@dimensionaledge.com] Skickat: den 25 september 2019 05:24 Till: postgis-users@lists.osgeo.org; Malm, Paul (Operations AIM) Ämne: Re: Lines crossing longitude 180 -180 re simply example, it might help if I got my (lon,lat) around the

[postgis-users] Lines crossing longitude 180 -180

Hi, I'm creating a table with LineStrings in SRID 4326 from lines with coordinates like this: S37323578E178560795 S38161687W179595994 S3827W17944 S38414463E179595994 S39394800E178553000 S40224800E178060600 S41504422E176164722 S42513000E17503 S43372622E174062536 S45135100E172134000

Re: [postgis-users] Could someone explain the difference

Thanks Martin, I could not get the “where” command to work in the join clause. So I made it simple for me. Delete from orig Where ST_contains(geom1, geom2) AND ST_lenght(geom2) I don’t know if this is more time/prosessor consuming than other solutions. Kind regards, Paul Från: postgis-users

[postgis-users] Could someone explain the difference

Hi, Could someone please tell me why I don't get this to work? I have one layer with lines and one layer with polygons. The lines are broken in the intersections of the polygons. I would like to save a new layer with all lines inside the polygons and update the orig layer without those lines:

Re: [postgis-users] breake lines

Hi, I made 2 tests. Drop TABLE IF EXISTS result; Create table result as WITH data AS ( SELECT * FROM "origdata" AS t(id, geom) ), cutter AS ( SELECT * FROM "cut_Line" AS t(geom) ) SELECT id, CASE WHEN cutting IS NULL THEN geom ELSE st_collectionExtract(ST_Split( geom,

Re: [postgis-users] breake lines

Hi, I’ve succeeded in creating the table as you suggested Martin (Thanks!). With some small modifications (CollectionExtract etc.) The problem is that I would like to transfer all attributes from the original file to the result file. I’ve tried so many ways but I can’t get it right. Drop table

Re: [postgis-users] breake lines

Thanks Lars, I will have this in mind, but for now I would like to learn SQL and will take a look at Topology later on. Kind regards, Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Lars Aksel Opsahl Skickat: den 6 september 2019 14:31 Till:

Re: [postgis-users] breake lines

Hi, This is how far I got today: LayerToBeSplitted is of type MultiLinestrings DROP TABLE IF EXISTS tmp; DROP TABLE IF EXISTS tmp2; CREATE TABLE tmp AS WITH data AS ( SELECT * FROM "LayerToBeSplitted" AS t ), cutter AS ( SELECT the_geom FROM "LayerToSplitWith" AS t ) SELECT *,

Re: [postgis-users] breake lines

Hi, If I understand your question correct, yes I would like to break all lines in the intersections with another layer. No I have not tried MultiLinestrings comprised of all lines that are intersecting the lines to be broken. Don’t know how to do this. ☹ This is how far I have come: CREATE

Re: [postgis-users] breake lines

If I split lines with another line layer, I will get a lot of duplicaded line segments since the lines could be intersected several times. It is not possible to use update on the table when splitting one geomety to two. I think it must be possible, but I’m not that skilled in SQL and I’ve not

Re: [postgis-users] Linestrings and MultiLiestrings to Linestrings

Yes, thanks! 3 sep. 2019 kl. 05:36 skrev Bo Victor Thomsen mailto:bo.victor.thom...@gmail.com>>: https://gis.stackexchange.com/questions/49950/converting-mulitlinestring-to-linestring-in-postgresql-with-postgis/49994 If you simply want to have a single type in your table it's much easier to

[postgis-users] Linestrings and MultiLiestrings to Linestrings

Hi, I have a table (bufferedlines) with a geometry column (the_geom) including both Linestrings and MultiLinestrings, what is the easiest way to change the_geom to a Linestring column? Kind regards, Paul ___ postgis-users mailing list

Re: [postgis-users] breake lines

Thanks, Simon! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Simon Norris Skickat: den 30 augusti 2019 18:14 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] breake lines You probably want ST_Split(). If you are working with your previous example, one way to

[postgis-users] breake lines

Hi, What PostGIS command do I use to breake linestrings or multilinestrings using another "breaking" table including linestrings or multilinestrings, without erasing any points or adding anything from the "braking" layer? Thanks in advance, Paul ___

Re: [postgis-users] st_witin

Thanks Martin, you confirmed what I suspected. A tolerance would have been nice to use. /Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Martin Davis Skickat: den 29 augusti 2019 18:06 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] st_witin My guess is

[postgis-users] st_witin

Hi, I have a layer with lines that I have buffered to a polygon layer, those polygons (not multipolygons) are unioned and containes holes. I would like to create a line layer (from another line layer) with the line parts that are within buffered area in the polygon layer. I've tried like this:

Re: [postgis-users] create holes in ocean polygons with island polygons

Thanks Darafei, perfect! Just for newbees like me if they read this mail: you have missed one “)” at the near end UPDATE public."Ocean" SET the_geom = coalesce(ST_Multi(ST_Difference("Ocean".the_geom, (select ST_Union(the_geom) from "Islands" as b where ST_Intersects( "Ocean ".the_geom,

[postgis-users] create holes in ocean polygons with island polygons

Hi, I have a table with ocean polygons and would like to create holes in those polygons with an island layer (including island polygons). I've tried this: UPDATE public."Ocean" SET the_geom = ST_Multi(ST_Difference("Ocean".the_geom, b.the_geom)) FROM "Islands" as b WHERE ST_Intersects( "Ocean

Re: [postgis-users] splitting linestrings with a polygon

Thanks, Nicolas! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Nicolas Ribot Skickat: den 20 augusti 2019 09:21 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] splitting linestrings with a polygon Hi, Keep the geometry column as multilinestring, as resulting

[postgis-users] splitting linestrings with a polygon

Hi, I have a problem with MultiLinestrings/Linestrings. I have one layer with Linestrings which I would like to split in the intersections of a polygon (MultiPolygon) in a polygon layer. I tried this: UPDATE public."linelayer" b SET the_geom = ST_Intersection(b.the_geom, p.the_geom) FROM

Re: [postgis-users] Interior rings

Thanks Paul! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Paul Ramsey Skickat: den 13 augusti 2019 17:16 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] Interior rings You’re going to want to chain a ST_DumpRings (turn polygons to rings) to an ST_Dump (turn

[postgis-users] Interior rings

Hi, I would like to create a table with all the interior rings (islands) from a table with multipolygons lakes. I've only come this far: CREATE TABLE "islands" AS SELECT (ST_InteriorRingn(the_geom, 2)).geometry AS the_geom FROM public."lakes"; But this is only collecting the first island in

Re: [postgis-users] Help with a selection query

Thanks, Hugues and Martin for helping a SQL newbee! 5 juli 2019 kl. 00:02 skrev Martin Davis mailto:mtncl...@gmail.com>>: This is a nice application for windowing. Try this: WITH data AS (SELECT * FROM (VALUES ( '902930' ), ( '902920' ), ( '902900' ), ( '903110' ), ( '913210' ), (

[postgis-users] Help with a selection query

I’ve sent this question to postgres user group without any success. Thats why I’m trying with you. I have a column named col (varchar) col could look like this 902930 902920 902900 903110 913210 913200 … I would like to remove an object that doesn’t end ‘00’ and if there are objects that

Re: [postgis-users] creating points from polygons, storing all attributes

Thanks everyone! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För didier peeters Skickat: den 4 juli 2019 14:46 Till: PostGIS Users Discussion Ämne: Re: [postgis-users] creating points from polygons, storing all attributes That’s because you named the new geometry «

Re: [postgis-users] creating points from polygons, storing all attributes

Thanks, I've tried that but get this error: ERROR: column "the_geom" is specified more than once SQL state: 42701 /Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Derek Watling Skickat: den 4 juli 2019 13:55 Till: PostGIS Users Discussion Ämne: Re: [postgis-users]

[postgis-users] creating points from polygons, storing all attributes

Hi, I would like to create a point table from a polygon table. One point in each polygon Query = CREATE TABLE NewPointLayer AS SELECT ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM OldPolygonLayer But how do I write the query to get all the attributes from OldPolygonLayer

Re: [postgis-users] simple geometry wiever in java

Thanks, everybody. I’m using the example in geotools quickstart, just chaning from reading a shape file to read a postgistable. 29 maj 2019 kl. 15:39 skrev Nicolas Ribot mailto:nicolas.ri...@gmail.com>>: Hi, Have a look at OpenJump (openjump.org) It can read a lot of

Re: [postgis-users] simple geometry wiever in java

Thanks, Luc! I mean a really simple viewer, justvViewing postgis data. Not a GIS system, it could be a code snippet/example of viewing postGis data. Kind regard, Paul Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Luciano Campagnolo Skickat: den 29 maj 2019 15:20 Till:

[postgis-users] simple geometry wiever in java

Hi, I've been spending the day with searching for a simple geometry/Gis viewer for postgis written in java. No WMS or shp. Does anyone know were I can download such an java project? King regards, Paul ___ postgis-users mailing list

Re: [postgis-users] error: by topology.topogeometry

Thanks, I will try your suggestions! /paul Skickat från min iPhone > 27 mars 2019 kl. 15:44 skrev Sandro Santilli : > >> On Tue, Mar 26, 2019 at 02:07:55PM +, paul.m...@lfv.se wrote: >> >> UPDATE merged SET topo_geom = topology.toTopoGeom(the_geom, 'roads_topo', >> 1, 0.001); > > [...]

[postgis-users] error: by topology.topogeometry

Hi, when I run this sql queries CREATE TABLE merged AS( SELECT fname, the_geom FROM public."RST_1813_UTM31N_WE_D_R_Line" UNION SELECT fname, the_geom FROM public."CTR_1813_UTM31N_WE_line"); UPDATE merged set the_geom = st_makevalid(the_geom); CREATE INDEX gid ON merged USING GIST ( the_geom );

[postgis-users] topologies

Hi, I'm new to postgis and db-handling overall. I'm having a problem with the example at: http://blog.mathieu-leplatre.info/use-postgis-topologies-to-clean-up-road-networks.html This is what I want to do; Merge 2 line layers together, and keep track on from which layer the lines came from

Re: [postgis-users] Problems with installing postgis in windows

Sorry it works now, I had used postgres "BIGSQL", not EDB installation! Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Malm, Paul (Operations AIM) Skickat: den 27 februari 2019 11:03 Till: postgis-users@lists.osgeo.org Ämne: [postgis-users] Problems with installing

[postgis-users] Problems with installing postgis in windows

Hi, I've tried to install Postgis for windows (postgis-bundle-pg11x64-setup-2.5.1-1.exe) with PostgreSQL11. But I receive this message at the end of the installation: ERROR: could not open extension control file "C:/POSTGR~1/pg11/../pg11/share/postgresql/extension/postgis.control": No such