Re: Composing raster tiles?

On Sat, 9 Nov 2024 at 11:52, Regina Obe wrote: > The main overhead I see with postgis raster are the following > > > >1. Limitation of raster sizes of about 1GB as I recall >2. Detoasting of data which is almost always needed >https://www.postgresql.org/docs/current/storage-toast.html

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 stat

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 func

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 d

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 colum

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 suividossier

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] A function of getting the Nth point of a line

Don't hesitate to read the postgis manual: http://postgis.net/docs/ST_PointN.html Nicolas On Mon, 13 Dec 2021 at 12:07, Shaozhong SHI wrote: > Any function for getting the Nth point of a line? For instance, 2nd, and > 3rd and etc. > > Regards, > > David > __

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 based

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 it

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 retu

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 'Va

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

I run it I get: > > =# SELECT * FROM region3_sim_storms_pg(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 functio

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 seco

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 extrac

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 between s

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 st_dwithin(t.sta

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, > > no

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 at

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 than

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 on

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 > Cheers

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

ting. > > ERROR: missing 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 &g

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 o

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 ( select

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 Nic

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, aborruso

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 to convert from one char

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 wrote: > Hi, > Long texts are not displayed in pgAdmin. > No entry for geometry columns usually means your geometri

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

s 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, allowing > >

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

17 at 22:36, Paul Ramsey 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 > wrote: > > Yes, tables are analyze

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

the stats are all up-to-date. > P. > > On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot > wrote: > > Hi, > > > > I have 2 tables, indexed and analyzed. > > One big (batiment) with 45M records, one small (tmp.tp1), with 83k > records > > (UNLOGGED table) >

[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 FR

Re: [postgis-users] Adding a sub-series or sub-index to output

Hi, You could use st_dumppoints directly to dump polygons points, then by joining on itself, generate the segments, using the dump.path column to keep a segment id for each polygon. (to adapt in case of multipg): with tmp as ( select 1::int as id, 'POLYGON((0 0, 1 0, 1 1, 0 0))'::geometry(polyg

Re: [postgis-users] DISTINCT geometry to a code

Hi, You could use a windows function to generate ids for the points: SELECT geom, row_number() over () as code FROM juli.savannaswdsp group by geom; Nicolas On 15 March 2017 at 15:49, juli g. pausas wrote: > Hi > I have a table with 945361 rows but the geometry (points) are distinct for > 20

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 other than Point? ST_M

Re: [postgis-users] Finding object that cover multiple polygons

Hello, For this kind of query, you can compute the area of the intersection of the building with each parcel, and order buildings according to the biggest intersection with row_number(): with tmp as ( select p.id, b.id, row_number() over (partition by b.id order by st_area(st_in

[postgis-users] pl/pgsql function to write table in parallel mode

Hi, Playing with new PG9.6rc1 / Pgis 2.3beta1, I found parallel query mode to be really efficient to process big tables. Unfortunately, it is not possible to directly create tables with parallel plan (create table as select...) (see: https://wiki.postgresql.org/wiki/Parallel_Query). It is possible

Re: [postgis-users] No Space Left on Device Error

Hi, There is no join condition between your bldgs and gee_ndvi_2013_merged (how many rows in this table ?) tables so your query is doing a cartesian product: all bldgs rows x all gee_ndvi_2013_merged rows. On 4 May 2016 at 12:35, Michael Treglia wrote: > Hi All, > > I'm new to the list and pr

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 trigger function which creat

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 ocean/lake(that gives depth of

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. > If you want to reproject

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 object

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 the

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

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" wrote: > >> What kind of client is plugged to your Java code ? >> Does it expect a raw json document to proces

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

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" wrote: > >> Hi Andy, >> >> Your Java ResultSet contains one big row representing your geoJson >> document

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 d

Re: [postgis-users] Finding points of intersection of a linestring with another multiolygon and linestring

Hi, Yes, you can use st_dumppoints to extract way's points and use these points in a query with seg_wp_intersection and zone_wp_intersection to find intersections. Nicolas On 1 October 2015 at 20:27, Trang Nguyen wrote: > I realize I didn’t clearly phrase my question: > > > > - Is the

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 pgr_dijkstra( > >

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 wrote: > Hi List, > > I have a pointcloud, and I want to turn it into a TIN (so I

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. N

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

gt; 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

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] 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 coordin

Re: [postgis-users] Topology & Attributes

(not the >> topology.toTopoGeom() >> as you mentioned ) >> >> When I look at the relation table its empty ? should I use >> topology.toTopoGeom() >> instead, and it will populate it ? >> >> 2. can you explain what the parentheses (special the 1, ...) part

Re: [postgis-users] Topology & Attributes

:57, Ofer Zilberstein 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 > wrote: > >> Hi, >> >> When you build the topology, for

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 t

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 wrote: > Hello, > > We use Postgre / Postgis da

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 interpo

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)?

t; Would you mind, if possible, giving 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 >

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 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 into my postgis database. >

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 wrote: > Hey list, > I'm a bit ashamed to be stuck with a seemingly trivial problem : > executing > ` > /usr/lib/postgresql/9.3/bin/shp2pgsql 2_poles.s

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 wrote: > Hi! > > I am using the ST_Dif

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 wrote: > Hello postgis users, > > I have a bounding box and need homogeneous polygons (rectangles) like a > matrix ins

Re: [postgis-users] pointcloud extentions

; > default_version = '1.0' > > module_pathname = '$libdir/pointcloud' > > relocatable = true > > superuser = true > > #requires = 'postgis' > > > > Would you please guide me how to install in windows > > > > > Thanks &

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 wrote: > Dear Sir, > > > > I installed postgis with opengeosuit4. 1 (OpenGeoSuite-latest.exe) and >

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

By the way, did you analyse the table ? On 29 July 2014 12:23, Nicolas Ribot 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: > > s

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 wrote: > Hello, > > > > I am using Postgre 9.3.4 with postgi

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 wrote: > Sorry! > > I cannot read manual > http://postgis.net/docs/reference.

Re: [postgis-users] polygons to linestrings

Hi Richard, You could use st_node on the polygon boundaries to rebuild unique, complete linestrings: Boundaries are dumped into linestrings in order to build a Multilinestring containing all PG boundaries. st_node is performed on this collection, then result is dumped into individual lines: with

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, ) Nicolas On 7 May 2014 15:56, Stephen Woodbridge wrote: > Linestrings are geometry objects. If you want your table t

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 wrote: > 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 property that is a pr

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 wrote: > My problem is the exterior ring of the polygon. There are points inside > of this ring > http://hpics.li/2807e0d > These poin

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 wrote: > Hello all, > > I am trying to get the boundary of a geometry(polygon) field in postgis > (multiple polygons in this fi

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

ey triangles and skeletonization for > future purposes. > To create the centerline a manual sketch is probably OK. Could I then just > move on with linear referencing? Which function should I use - most seems > to be percentages of a linestring. > > Karl > > Den lørdag den 30. november

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 perpendicul

Re: [postgis-users] [postgis-devel] Translation setup in transifex

Hi George, Very nice and effective tool for translation ! Nicolas On 26 November 2013 14:38, George Silva wrote: > Hello everyone, > > The translation project has been setup in transifex. > > https://www.transifex.com/projects/p/postgis-1/ > > Everyone that wishes to collaborate will be welcome

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 wrote: > @ Nicolas : i put the CIDR mask after the address in the file. In vain... > > @ Morten : Yes i'm sure : i saved the pg_hba.conf. corr

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

Hi, The actual pg_hba.conf file is in the C:\Program Files\PostgreSQL\9.1\data folder Nicolas On 22 November 2013 11:50, laurent wrote: > Hello, > > So. In the folder C:\Program Files\PostgreSQL\9.1\share, i have a file > called "pg_hba.conf.sample". > > Please find below the raw sentences : >

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

, Denis Rouzaud 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 o

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

om) n > INTO geom; > RETURN geom; > END; > $BODY$ > LANGUAGE plpgsql; > > But I wondered if there was a more direct way. > > Cheers, > > Denis > > > On 21. 11. 13 12:42, Nicolas Ribot wrote: > > Hi Denis, > > In your e

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 wrote: > Hello Rémi, > > I was hoping a simplest request without enabling topology but

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 geometry_c

Re: [postgis-users] how to move geometries to a fraction of their position?

Hi, Look at st_translate, st_scale and st_transcale. Nicolas On 5 November 2013 12:51, Pietro Rossin wrote: > I have a geomedia mdb that I moved to postgis. > All geometries (metric coordinates) are hundred times greater on x and y of > what they should be.. > Let's say if a coordinate sould be

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

mpSegments(line2) > FROM ST_GeomFromText('MULTILINESTRING((12 1, 13 1, 14 2, 15 4),(1 1, 2 2,3 > 3))') AS line1,ST_GeomFromText( 'LINESTRING(12 1, 13 1, 14 2, 15 4)') AS > line2 > > > > > ////////// > > > > > 2013/10/30

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 wrote: > Hey, > is somebodyers aware of a function breaking a linestring into a set of > segments (mathe

  1   2   >