Re: Display heatmap with spatial index and values

yes On Wed, 22 May 2024 at 10:20, Shaozhong SHI wrote: > Can heatmp be displayed with spatial index and values? > > Regards, > > David >

Re: [postgis-users] QGIS freezes when saving table with AFTER INSERT or UPDATE

Hello, A trigger is executed in a single transaction: "The execution of an AFTER trigger can be deferred to the end of the transaction, rather than the end of the statement, if it was defined as a *constraint trigger*. In all cases, a trigger is executed as part of the same transaction as the

Re: [postgis-users] Distance from Rast and LineString overlay

On Fri, 27 Oct 2023 at 10:18, Eloi Ribeiro via postgis-users < postgis-users@lists.osgeo.org> wrote: > Hi Nicolas, > > Thanks for your reply. Your solution worked perfectly and faster than > anything I was trying, overlaying with Polygons or Raster. Using > Linestrings together with Subdivide

Re: [postgis-users] Distance from Rast and LineString overlay

Hello, I would polygonize the raster with gdal_polygonize in the database, as it is very fast. Then extract the boundaries (linestring), then subdivide them for better performance. >From there, I would intersect the 1km lines for each point with forest boundaries and sort intersection points by

Re: [postgis-users] PGAdmin4 - not getting swamped by geometry columns in data view?

Hello, You can choose the default columns size in PgAdmin preferences. It defaults to "column data" but you can set it to be "column name": "If set to 'Column data' columns will auto-size to the maximum width of the data in the column as loaded in the first batch. If set to 'Column name', the

Re: [postgis-users] (no subject)

Hello, Yes you can by creating a view using UNION ALL (or UNION if you want to filter out duplicate lines) between tables you want to use, for instance: create or replace view myview as select suivi_dossier as suivi_dossier, commune as commune, geom from table1 UNION ALL select

Re: [postgis-users] PostGIS 3.2.0, The Olivier Courtin Release

A great initiative We miss Olivier a lot. Nicolas On Sat, 18 Dec 2021 at 06:46, Regina Obe wrote: > The PostGIS Team is pleased to release PostGIS 3.2.0, the Olivier Courtin > release. > This release is dedicated to Olivier Courtin, a long time PostGIS developer > who passed on in March 2020.

Re: [postgis-users] polygons inside polygon

Hi David, You can use st_isValidReason(geom) to inspect an invalid geometry. Predicates like st_contains, st_overlaps, st_intersects, etc. can give you information about relationship between geometries. They all originate from st_relate (http://postgis.net/docs/ST_Relate.html) Nicolas On Wed, 1

Re: [postgis-users] polygons inside polygon

= (SELECT outer_p FROM ( > > SELECT DISTINCT p1.fid, p2.fid IS null AS outer_p > > from org p1 > > LEFT JOIN org p2 ON p1.fid <> p2.fid AND st_within(p1.the_geom, > p2.the_geom) WHERE p1.fid = org.fid) AS tmp) > > > > But that is not correct, do you

Re: [postgis-users] polygons inside polygon

Hi, What about a simple left join on the table searching for polygons contained inside other polygon ? Polygons that are contained inside other polygons will have false for the outer_pg column and true if they are not contained (ie are outer polygons). You can then create the tables you want

Re: [postgis-users] Regarding support of postgis_pointcloud in Centos 7

Hello, pointcloud and postgis_pointcloud are separate extensions you have to install. They do not come with postgis. Nicolas On Fri, 16 Jul 2021 at 17:38, mridu rajkhowa wrote: > Hello, > > I have been trying to install postGIS 3.1 along with PostgreSQL 13 and > able to successfully install

Re: [postgis-users] Postgis 3.1.0 st_makevalid crashes the PostgreSQL Server

With geos-master (3.10.0dev-CAPI-1.15.0), the buffer query from json polygon works and returns a valid polygon, but makevalid on empty geoms still crashes PG Nicolas On Tue, 26 Jan 2021 at 12:01, Nicolas Ribot wrote: > Hi, > > I could also reproduce it on MacOS > (POSTGIS=&q

Re: [postgis-users] Postgis 3.1.0 st_makevalid crashes the PostgreSQL Server

Hi, I could also reproduce it on MacOS (POSTGIS="3.1.0 5e2af69" [EXTENSION] PGSQL="130" GEOS="3.9.0-CAPI-1.16.2" SFCGAL="1.3.9" PROJ="7.2.0" GDAL="GDAL 3.2.0, released 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER) The buffer operation

Re: [postgis-users] Contradiction between ST_IsValid() and ST_IsValidReason() ?

Hello What is Postgis/geos version ? Yes, one or two examples of these strange geometries would be nice. Nicolas On Wed, 23 Dec 2020 at 11:20, Andreas Neumann wrote: > Hi, > > I have the strange situation that ST_IsValid() claims a geometry is not > valid, but then ST_IsValidReason() says

Re: [postgis-users] ST_AsMVTGeom AND table name as parameter in pl/psql

13,2361,3138,'r3sim_fort_geom'); > ERROR: EXECUTE of SELECT ... INTO is not implemented > HINT: You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... > AS instead. > CONTEXT: PL/pgSQL function > region3_sim_storms_pg(integer,integer,integer,text) line 5 at EXECUT

Re: [postgis-users] ST_AsMVTGeom AND table name as parameter in pl/psql

(and you can still store the result in a variable with the construct EXECUTE 'your query here' into result;) nicolas On Wed, 16 Sep 2020 at 16:40, Nicolas Ribot wrote: > Hi, > > You have to enclose the whole query into a string and execute it, not just > only the second

Re: [postgis-users] ST_AsMVTGeom AND table name as parameter in pl/psql

Hi, You have to enclose the whole query into a string and execute it, not just only the second CTE (mvtgeom). It should be: BEGIN EXECUTE format('WITH bounds as ( ', ...) Nicolas On Wed, 16 Sep 2020 at 16:32, James McManus wrote: > I'm trying to develop a plpgsql function that would

Re: [postgis-users] Spatial linking or selection

ee its wkt or proj4 definition in spatial_ref_sys table to figure out the SRS unit) https://postgis.net/docs/ST_DWithin.html > > Regards, > > Shao > > On Fri, 15 May 2020 at 11:32, Nicolas Ribot > wrote: > >> Hi, >> >> You can test for proximity betw

Re: [postgis-users] Spatial linking or selection

Hi, You can test for proximity between start and end points Create spatial indexes on st_startpoint(geom) and st_endpoint(geom) on your line features table (also index starting and ending points column in table one) select t.id, r.id, r.geom from start_end_table t join roads r on

Re: [postgis-users] Help with SQL

Sorry for the missing group by part: Query should be: select p.id, (array_agg(o.id order by height_m))[1] as heighest_id from polyobstacles p join obstacles o on st_contains(p.geom, o.geom) *group by p.id ;* Nicolas On Mon, 9 Mar 2020 at 10:38, Felix Kunde wrote: > Hi Paul, > >

Re: [postgis-users] Help with SQL

Hi, Something like accumulating ids of obstacles for each polygon, ordered by height, and taking the first element of the array: select p.id, (array_agg(o.id order by height_m))[1] as heighest_id from polyobstacles p join obstacles o on st_contains(p.geom, o.geom); Nicolas On Mon, 9 Mar 2020

Re: [postgis-users] Max size of rasters?

Hello, > > > We are trying to create somewhat large rasters (30-40 bands of data > comprising a few GB of data uncompressed). At some point the import into > PostGIS is failing. Supposedly there is a 1GB field size limit in Postgres. > Is it really so that you cannot have raster fields larger

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

Hi, Keep the geometry column as multilinestring, as resulting splitting can produce such objects, but add a st_multi after the intersection, on the update clause: ... SET the_geom = st_multi(ST_Intersection(b.the_geom, p.the_geom)) It will force geometries to be multi, even if it only contains

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

Hi, Have a look at OpenJump (openjump.org) It can read a lot of spatial databases and allows to type your SQL query to visualize your data. Nicolas On Wed, 29 May 2019 at 15:20, Luciano Campagnolo < luciano.campagn...@gmail.com> wrote: > And uDig is a more simple GIS viewer/desktop GIS >

Re: [postgis-users] Selecting as GeoJSON with Transformation

issing FROM-clause entry for table "trails_01" > LINE 3: ST_AsGeoJSON(ST_Transform(ST_SetSRID(trails_01.geom... > > which searches lead me to there is an ordering issue > > Thanks! > Sean > > On Mon, Feb 11, 2019 at 4:00 AM Nicolas Ribot > wrote: > >> &

Re: [postgis-users] Selecting as GeoJSON with Transformation

On Fri, 8 Feb 2019 at 23:48, Sean Montague wrote: > It has been many years since I last worked with postGIS, I don't do GIS > for a living anymore but I still like to have fun with it. I'm looking to > export some old data I have as GeoJSON and projecting it to display in a > Google Map Overlay.

Re: [postgis-users] Improvement suggestion

Hi, Did you cut your big polygons with st_subdivide before intersecting them ? It usually speeds up queries by several orders of magnitude. Nicolas On Mon, 3 Dec 2018 at 18:41, Paul van der Linden wrote: > I indeed know that trick to eliminate an st_intersection, but that comes > at the cost

Re: [postgis-users] Extend linestring to snap to polygon

(Line extension here is based on the shortest distance between line and polygon. To extend in the direction on line segments, you can refer to this list, message "Finding closet intersect point along direction of line", april 10 2013) Nicolas On Mon, 15 Oct 2018 at 09:58, Nicolas Ri

Re: [postgis-users] Extend linestring to snap to polygon

Hi, you can extract the exterior of the polygon (st_boundary), use linear referencing function st_lineLocatePoint to find the projection of line's start and end points then add the points to the line: with pg as ( select 'POLYGON((0 0, 5 0, 3 5, 0 0))'::geometry as geom ), line as (

Re: [postgis-users] PostGIS Topology Error

Hi, Indeed, Postgis Topology works well to build topo from clean geometries, but can fail if input geometries have topological errors. It could be tricky to clean data with just postGIS and sql queries. Topological GIS, like GRASS, can load PostGIS data and clean them at a given precision HTH

Re: [postgis-users] Increase KNN efficiency

> Behalf Of *Vispo Leblanc > *Sent:* Thursday, May 31, 2018 12:05 PM > *To:* postgis-users@lists.osgeo.org > *Subject:* Re: [postgis-users] Increase KNN efficiency > > > > Thanks Nicolas, this is *way better*, I guess my previous query was > calculating all distance between all el

Re: [postgis-users] Increase KNN efficiency

Hi, Put the KNN operator in a LATERAL subquery, using LIMIT x to limit NN to x elements: SELECT p1.id, t.id AS id2, t.dist FROM parc p1 CROSS JOIN LATERAL (SELECT p2.id, st_distance(p1.geom, p2.geom) AS dist FROM parc p2

Re: [postgis-users] Build direction in a network, only from a starting point

Hi, A recursive CTE query will allow you to walk the network, checking if startPoint/endPoint of lines are connected as you want. st_reverse could then be used to correct these lines. (http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html) Nicolas On 9 May 2018 at 08:23,

Re: [postgis-users] postgis-users Digest, Vol 194, Issue 4

On 9 April 2018 at 18:02, Aaron Edwards wrote: > Hi All, > > Thanks for replying. > > The data is being read from SQL Server, where it is encoded in Latin1. > I'm reading it from SQL Server, and writing it to postgis using a .NET > process. I'm not doing anything special

Re: [postgis-users] splitting a linestring with points

Hi, One very efficient way of splitting lines is to use Linear Referencing functions (http://postgis.net/docs/reference.html#Linear_Referencing) st_lineLocatePoint and st_lineSubstring will allow you to cut your linestring according to points locations along the line. Nicolas On 9 February 2018

Re: [postgis-users] ogr2ogr oracle to postgis - emtpy geom column

(concerning QGIS, its a DB driver option that allows to resolve tables with GEOMETRY column) [image: Inline images 1] Nicolas On 9 January 2018 at 18:46, Nicolas Ribot <nicolas.ri...@gmail.com> wrote: > Hi, > Long texts are not displayed in pgAdmin. > No entry for geometry

Re: [postgis-users] ogr2ogr oracle to postgis - emtpy geom column

Hi, Long texts are not displayed in pgAdmin. No entry for geometry columns usually means your geometric type is GEOMETRY. You can cast it to POLYGON or MULTIPOLYGON. You can test your geo column: select st_summary(shape_poly), st_npoints(shape_poly) from au_alkis_unit; Nicolas On 9 January 2018

Re: [postgis-users] Bad plan without && operator

way to change this may be > > ALTER TABLE tmp.tp1 SET STATISTICS 1; > ALTER TABLE ref.batimet SET STATISTICS 1; > ANALYZE tmp.tp1; > ANALYZE ref.batimet; > > By default table is estimated by histogram of 100 cells, basically grid > 10x10. 1 changes it to 100x100, a

Re: [postgis-users] Bad plan without && operator

17 at 22:36, Paul Ramsey <pram...@cleverelephant.ca> wrote: > If you change the small table into a normal table (not unlogged) does > the problem go away? Could be an interesting side effect of > "unlogged"? > > On Wed, Dec 6, 2017 at 1:14 PM, Nicolas Ribot <nico

Re: [postgis-users] Bad plan without && operator

NALYZE' and the stats are all up-to-date. > P. > > On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot <nicolas.ri...@gmail.com> > wrote: > > Hi, > > > > I have 2 tables, indexed and analyzed. > > One big (batiment) with 45M records, one small (tmp.tp1), with

[postgis-users] Bad plan without && operator

Hi, I have 2 tables, indexed and analyzed. One big (batiment) with 45M records, one small (tmp.tp1), with 83k records (UNLOGGED table) When performing a spatial join, the planner chooses the smallest table for seq scan only if I add a && condition with st_intersects: EXPLAIN SELECT p.id, b.id

Re: [postgis-users] Acessing M-Part of objects

Hi, You can use st_dumpPoints (http://postgis.net/docs/ST_DumpPoints.html) to dump points out of your geometries Nicolas On 11 February 2017 at 13:15, Walter Nordmann wrote: > Hi, > > just playing around with ST_EffectiveArea > > how can i access the M-Part of an object

Re: [postgis-users] handling invalid geometry within a trigger

Hi, In plpgsql, you can catch the error that will be generated by such a polygon: http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Nicolas On 15 February 2016 at 15:43, LPlateAndy wrote: > Hi, > > I have a postgresql

Re: [postgis-users] Creating touching depth areas from gridded data

Hi Hemant, You may find some tips to simplify polygons while keeping topology on Postgis wiki here: https://trac.osgeo.org/postgis/wiki/UsersWikiMain § Examples of Spatial SQL Nicolas On 3 February 2016 at 19:34, Hemant Bist wrote: > Hi, > I have gridded data for

Re: [postgis-users] lat,lon or lon,lat in WGS84 WKT

Hi, To reverse geometry coordinates, use ST_FlipCoordinates or ST_SwapOrdinates Nicolas On 17 November 2015 at 18:08, Paul Ramsey wrote: > We Leave It To You, in that the ordering in the WKT will be reflective > of the ordering you've stored your data in. > However.

Re: [postgis-users] visualising 3d lines in postgis with SFCGAL

Hi, QGis has 2 nice plugins (that I know of) to display 3D data coming from PostGIS: Horao (http://oslandia.github.io/horao/) and QGis2ThreeJS ( https://github.com/minorua/Qgis2threejs), that produces a ThreeJS web page that you can export. These plugins allow to drape a DEM and to plot 3D

Re: [postgis-users] Queries on partitioned table not behaving as expected

Hi, AFAIU, the restriction on partitioned tables is even bigger. From PG doc ( http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION) : "The following caveats apply to constraint exclusion: - Constraint exclusion only works when

Re: [postgis-users] How do I handle a ResultSet from a java request?

Hi Andy, Your Java ResultSet contains one big row representing your geoJson document. You can pass it to the client by building a string from the resultset: String geojson = resultSet.getString(1); Be careful to the potential big size of the String: if you expect a big geojson document from the

Re: [postgis-users] How do I handle a ResultSet from a java request?

he data back with the > results on the map via a geojson. > > Im working on a "full stack' demonstration of map.. database.. and server.. > On Oct 6, 2015 9:02 AM, "Nicolas Ribot" <nicolas.ri...@gmail.com> wrote: > >> What kind of client is plugged to your Java code ? >&

Re: [postgis-users] How do I handle a ResultSet from a java request?

l be passing only about 40 point > features first. > > I will make note of and go look at the stream... as well.. > > Andy > On Oct 6, 2015 8:53 AM, "Nicolas Ribot" <nicolas.ri...@gmail.com> wrote: > >> Hi Andy, >> >> Your Java ResultSet conta

Re: [postgis-users] Need to combine several multilinestrings into one

Hello, Use st_collect(pt.geom) Nicolas On 28 September 2015 at 22:05, Bistrais, Bob wrote: > I am trying to create one multilinestring from several multilinestrings. > These are the geom results from a pgRouting query: > > > > SELECT pt.geom > > FROM

Re: [postgis-users] PointCloud to Delaunay Triangles

Hi, You may use a subquery to solve your issue: select st_collect(geom) from (select pc_explode(pa)::geometry as geom from tablename where cloud_id = 994) as t; Nicolas On 5 August 2015 at 13:59, Jonathan Moules j.mou...@hrwallingford.com wrote: Hi List, I have a pointcloud, and I want to

Re: [postgis-users] Quick/unobstrusive way to determine if a geometry has curve elements?

Hi Andrea, You could use a PLPGSQL function to catch error for curved geometries and return the geometry itself in case of error. Then, either you put all your generic code in this function, or you just write a wrapper around st_simplify and call it from your SQL code, according to your needs.

Re: [postgis-users] Fwd: Partitionning using geometry

Hi Remy, As far as I understood table partitionning has one limiting caveat for some usages (from: http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html): Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example,

Re: [postgis-users] Fwd: Partitionning using geometry

Nicolas, great answer. At least it gives some hope, because it is possible to compute value in plpgsql function and create on the fly a querry with those values hard written. I still don't know if it would work with geometry tough Thanks, Rémi-C 2015-04-02 10:59 GMT+02:00 Nicolas Ribot

Re: [postgis-users] Need help on basic concepts, I just need really simple calculations

Hi Aaron, SRID is used to set a consistent coordinate system to the data. It refers to an EPSG Coordinate Reference System (CRS). For instance, EPSG:2600: ( http://spatialreference.org/ref/epsg/2600/) is a metric CRS. Coordinates (146.0, 138.19) are not valid in SRID 2600 (nor in lat/long

Re: [postgis-users] Topology Attributes

what the parentheses (special the 1, ...) part mean... join pe.parcelle p1 on *(1, (p1.topo).id)* = (r1.layer_id, r1.topogeo_id) Thanks in advanced ... Ofer On Mon, Feb 9, 2015 at 11:05 AM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, As usual with SQL, there must be several other

Re: [postgis-users] Topology Attributes

:57, Ofer Zilberstein zilberstein.o...@gmail.com wrote: Much Thanks, First I will try to understand the join that you did and then try to implement it... Ofer On Sun, Feb 8, 2015 at 12:44 PM, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, When you build the topology, for instance

Re: [postgis-users] Topology Attributes

Hi, When you build the topology, for instance with topology.toTopoGeom(), you get back a TopoGeometry object for each polygon added to the topology. This TopoGeometry is used to keep a link between your polygon object and all the topological elements composing it. The relation table inside the

Re: [postgis-users] Get parallel some route.

Hi Guillaume, Not sure I understand what you want to achieve. Can you describe it, maybe with an image. (or in french) Non-simple lines can be cut at their intersection to make them simple. Nicolas On 28 January 2015 at 09:16, Guillaume ARNAUD guillaume.arn...@cg82.fr wrote: Hello, We

Re: [postgis-users] Evenly distributing a point set

Hello, You could build the linestring from points (makeLine(geom order by time)) then , with linear referencing (st_lineInterpolatePoint, st_linelocatePoint), you can divide your linestring by the distance you want, creating new points. Then, if you need to keep time information, you could

Re: [postgis-users] Doubts dissolving geometries

Hi, You can generate an id on-the-fly: SELECT 1 as id, ST_union(st_buffer(emt_paradas.geom, 500)) FROM emt_paradas Nicolas On 26 November 2014 at 19:14, Luís Miguel Royo Pérez luis.miguel.r...@gmail.com wrote: Hi everyone, I'm trying to make a quite simple query in Postgis but I have

Re: [postgis-users] Import gpx files into Postgis db (windows)?

a sample line or two about how someone could create a new table in PostGIS from the GPX please? The examples in the documentation aren't that clear (or perhaps I just don't understand them!). Regards James On 3 November 2014 08:06, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hello

Re: [postgis-users] Import gpx files into Postgis db (windows)?

Hello, You can use GDAL/OGR, (ogr2ogr): http://www.gdal.org/drv_gpx.html Nicolas On 31 October 2014 18:32, dandrigo lcel...@latitude-geosystems.com wrote: Dear all, I'm working with windows 8 postgis 2.1. I have several gps (gpx) files. I would like to import directly those gpx files

Re: [postgis-users] shp2pgsql -D not working properly

Hi, -D option should appear just after shp2pgsql, not after the shape and table parameters, no ? Nicolas On 19 September 2014 19:29, Rémi Cura remi.c...@gmail.com wrote: Hey list, I'm a bit ashamed to be stuck with a seemingly trivial problem : executing `

Re: [postgis-users] ST_Difference query returns a dissolved result

Hi, Your multipolygon is invalid: PG parts cannot share an edge in a MultiPolygon. Postgis unions the two parts during the st_difference process. Geometric operation should be called only on valid objects. Nicolas On 4 September 2014 21:20, Max Demars burton449...@gmail.com wrote: Hi! I

Re: [postgis-users] boundingbox into vector matrix

Hi, A useful function here: http://gis.stackexchange.com/questions/16374/how-to-create-a-regular-polygon-grid-in-postgis . Nicolas On 25 August 2014 12:37, Christopher Braune chbra...@uni-potsdam.de wrote: Hello postgis users, I have a bounding box and need homogeneous polygons

Re: [postgis-users] pointcloud extentions

Hi, This extension is not part of standard postgis installation (afaik). You have to install it manually: https://github.com/pramsey/pointcloud Nicolas On 20 August 2014 11:37, Mohammed Kabeer mkab...@tamimicg.com wrote: Dear Sir, I installed postgis with opengeosuit4. 1

Re: [postgis-users] Accessing NWS poly data with lat/lon input

Hello Considering your geometric column is named geom: select geom from polys where st_contains(geom, st_setSRID(st_makePoint(lon, lat), 4326)); (if several polygons contain this point, they will all be returned. Adding LIMIT 1 at the end of the query will force to get only one result. Nicolas

Re: [postgis-users] Postgis intersect query with limit does not use spatial index

Hi, The planner considers a seq scan is faster when only one record is expected. You can disable seq scan for this query and force the index usage by setting: set enable_seqscan to off; Nicolas On 29 July 2014 12:05, Alex Reitbort a...@skylinesoft.com wrote: Hello, I am using Postgre

Re: [postgis-users] Postgis intersect query with limit does not use spatial index

By the way, did you analyse the table ? On 29 July 2014 12:23, Nicolas Ribot nicolas.ri...@gmail.com wrote: Hi, The planner considers a seq scan is faster when only one record is expected. You can disable seq scan for this query and force the index usage by setting: set enable_seqscan

Re: [postgis-users] GeomFromText

Hi Antti, You can load the postgis legacy.sql file to restore deprecated function names (though it would be better to adapt your application to use new, conforming names) Nicolas On 18 July 2014 12:26, Antti J. Lind anttij.l...@birdlife.fi wrote: Sorry! I cannot read manual

Re: [postgis-users] Geometry vs Linestring

Hi, You could also cast the call to st_makeline into a specific geometry type, with some SRID (or 0 if data has no SRID) : ... ST_MakeLine(seeds.geom)::geometry(Linestring, srid) Nicolas On 7 May 2014 15:56, Stephen Woodbridge wood...@swoodbridge.com wrote: Linestrings are geometry

Re: [postgis-users] Point inside a polygon

Hi, Use st_pointOnSurface ( http://postgis.net/docs/manual-2.1/ST_PointOnSurface.html) Nicolas On 2 May 2014 06:32, J.Alejandro Martinez Linares isla...@infomed.sld.cuwrote: Hi people, i need your help, i was using st_centroid to get a point that be inside a polygon butr st_centroid got a

Re: [postgis-users] Erase from PostGIS line table using polygon table (like Erase in ArcGIS)

Hi, You could add a distinct clause after st_difference to filter out duplicate results. It may be enough to compare geometries bbox instead of real geometries, depending on your dataset: create table roadsdifference as ( with diff as ( select r.osm_id as osm_id, ST_Difference(r.geom,c.geom)

Re: [postgis-users] To correct Exterior Rings

Hi, You should try st_buildArea, or st_polygonize, as suggested by Remi. It corrects these kinds of errors. Nicolas On 11 February 2014 17:21, Richard LEHAUT richard.leh...@crpf.fr wrote: My problem is the exterior ring of the polygon. There are points inside of this ring

Re: [postgis-users] Getting boundary of set of polygons

Hi, You should try st_makePolygon, or st_buildArea depending on the result you want (keeping holes or removing them). Nicolas On 27 January 2014 13:32, Tom McCallum ter...@googlemail.com wrote: Hello all, I am trying to get the boundary of a geometry(polygon) field in postgis (multiple

Re: [postgis-users] Create equally spaced lines within a polygon

Nicolas Ribot: Hi, You should look at this french article for skeletonization of irregular polygons. The st_delaunayTriangles method can also help. Note that the middle line of a polygon is not always a straight line. Then, using linear referencing, it should be easy to walk the middle line

Re: [postgis-users] Create equally spaced lines within a polygon

Hi, You should look at this french article for skeletonization of irregular polygons. The st_delaunayTriangles method can also help. Note that the middle line of a polygon is not always a straight line. Then, using linear referencing, it should be easy to walk the middle line to create

Re: [postgis-users] Qgis/Postgis : impossible to open a project

Try to put the CIDR mask after the address, in your configuration file: # IPv4 local connections: hostall all 192.167.40.0/24 trust hostall all 192.167.40.92/32 trust # IPv6 local connections: hostall all

Re: [postgis-users] Qgis/Postgis : impossible to open a project

Look at postgresql.conf and search for the listen parameter: it lists hosts that can connect to the backend. Nicolas On 22 November 2013 15:05, laurent lcel...@latitude-geosystems.com wrote: @ Nicolas : i put the CIDR mask after the address in the file. In vain... @ Morten : Yes i'm sure : i

Re: [postgis-users] Difference on check constraints on colum type between Pgis 1.5 and 2.0 ?

Hi David, Yes: the new geometry type definition now allows contraints to be carried by the column itself. The typmod geometry(MultiPolygon,4326) forces the column to respect the object type (Multipolygon) and a SRID. Its like using varchar(30), for instance. You will also notice that

Re: [postgis-users] simplifying (homogenize) a polygon

Hi Denis, In your example, I don't understand how the parts of your multipolygons can be adjacent. Are they valid ? Do you have a visual example ? Thank you Nicolas On 21 November 2013 07:32, Denis Rouzaud denis.rouz...@gmail.com wrote: Hello Rémi, I was hoping a simplest request without

Re: [postgis-users] simplifying (homogenize) a polygon

if there was a more direct way. Cheers, Denis On 21. 11. 13 12:42, Nicolas Ribot wrote: Hi Denis, In your example, I don't understand how the parts of your multipolygons can be adjacent. Are they valid ? Do you have a visual example ? Thank you Nicolas On 21 November 2013 07:32, Denis

Re: [postgis-users] simplifying (homogenize) a polygon

, Denis Rouzaud denis.rouz...@gmail.com wrote: wow, thanks a lot! indeed, much much nicer! thanks again, Denis On 21. 11. 13 15:47, Nicolas Ribot wrote: Thanks for the explanation. You could correct the multipolygons in one step, by using the st_buildArea function, that merges a linework

Re: [postgis-users] utility function : linestring - segment[] or setof segment

Hi, I don't think such a function exists in postgis. (To achieve that, I usually dump line points and create new linestrings with vertices n, n+1) Nicolas On 30 October 2013 10:59, Rémi Cura remi.c...@gmail.com wrote: Hey, is somebodyers aware of a function breaking a linestring into a set of

Re: [postgis-users] New function ST_DumpSegments?? (was : utility function : linestring - setof segment)

// 2013/10/30 Rémi Cura remi.c...@gmail.com Thanks, wanted to do it the clean way (geos accelerated maybe?), but not so much a big deal, I'll go you way. Cheers, Rémi-C 2013/10/30 Nicolas Ribot nicolas.ri...@gmail.com Hi, I don't think such a function exists in postgis. (To achieve

Re: [postgis-users] st_split

Hi, The points are not on the line, probably due to rounding errors. st_contains(line, point) returns false. Nicolas On 28 October 2013 18:07, franco base frenk.ca...@gmail.com wrote: Hi. I have a set of linestring and point on linestring. I want cut the linestring on the point. I can have

Re: [postgis-users] st_split

line to point (won't work the other way) Cheers, Rémi-C 2013/10/28 Nicolas Ribot nicolas.ri...@gmail.com Hi, The points are not on the line, probably due to rounding errors. st_contains(line, point) returns false. Nicolas On 28 October 2013 18:07, franco base frenk.ca...@gmail.com

Re: [postgis-users] navigation trough topology graph

Hi, The WITH RECURSIVE construct allows to navigate a graph with good performance: http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html http://www.postgresql.org/docs/9.2/static/queries-with.html Nicolas On 21 October 2013 15:19, George Silva georger.si...@gmail.com wrote: I

Re: [postgis-users] ST_Collect

Hi, you could store initial information in a array of values of desc_string or other attribute, using array_agg function (or create a concatenated string using string_agg function): ... SELECT a.poi_id, a.geom, array_agg(desc_string) as descs, St_collect(b.geom) AS b_geom Nicolas

Re: [postgis-users] ST_Collect

Carsten 2013/10/11 Nicolas Ribot nicolas.ri...@gmail.com Hi, you could store initial information in a array of values of desc_string or other attribute, using array_agg function (or create a concatenated string using string_agg function): ... SELECT a.poi_id, a.geom, array_agg

[postgis-users] SQL Editor with functions autocompletion

Hi, Netbeans IDE provides a nice SQL Editor, with syntax coloring, autocompletion, query execution at cursor, etc. But it lack functions autocompletion (especially postGIS functions) Here is an entry to the Users Wiki (http://trac.osgeo.org/postgis/wiki/UsersWikiNbSqlFunctionsEditor) describing

Re: [postgis-users] service areas ( I want a polygon of the areas that only have one provider)

Hi, If it is the first case your dataset does not contain such polygons. You could identify polygons with no overlapping with others with a query: select gid from test t where not exists ( select 1 from test t1 where t1.gid t.gid and st_intersects(t1.geom, t1.geom) ); If it is

Re: [postgis-users] Optimize query

Hi, I imagine you define suitable index on raster table ? You could maybe try to add a functional index on ST_VALUE(rast,geom) != 0. I'm wondering if grouping by geom is very efficient ? If your data are clearly separated, you could try to group by the geometry's bbox instead, to see if it

Re: [postgis-users] split linestring

Hi, Yes, st_line_substring creates a shorter line, cutting the original line at a certain index. St_split will split the line in two parts if you split by a point. Nicolas On 7 August 2013 12:52, ranjitsaurav ranjitsau...@gmail.com wrote: Hi nicolas, thank you for the reply. i want to ask

Re: [postgis-users] Split linestring into two line

If you want 2 separate, parallel lines, from one input line, st_offsetCurve may help: http://postgis.net/docs/manual-2.0/ST_OffsetCurve.html Nicolas On 8 August 2013 09:55, Hugues François hugues.franc...@irstea.fr wrote: Hello, It is hard to give you an answer if we don't know what exactly

Re: [postgis-users] split linestring

Hi, Linear referencing st_line_substring function allows you to do that: http://postgis.net/docs/manual-2.1/ST_Line_Substring.html Nicolas On 7 August 2013 10:46, ranjitsaurav ranjitsau...@gmail.com wrote: hi i am new to the postgis. i would like to know if there is a way to split the

Re: [postgis-users] Problems installing on Linux

Hi, What is the *first* error message returned by the command: psql -d james_test -f /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql Nicolas On 5 August 2013 19:15, James David Smith james.david.sm...@gmail.com wrote: Hi there, I'm trying to make and compile PostGIS 2.0 with PostgreSQL

Re: [postgis-users] LineJoiner

Hi, You could use st_linemerge on the line dataset, first collecting it into a multilinestring then dumping the result into elementary linestrings: select (st_dump(st_linemerge(st_collect(geometry.geom from line; Nicolas On 24 July 2013 16:10, Andreas Neumann a.neum...@carto.net wrote:

  1   2   >