RE: Continuing on clustering queries

to force it to a specific type using some additional PostGIS functions Cheers Karsten - I've adapted some of your recommendations. One of the targets of this effort is to add a layer to an existing QGIS project: various energy infrastructure of the U.S./North America. So,

Re: [postgis-users] Missing ESRI authority SRIDs in 'spatial_ref_sys'

OJECTION["Hamme r_Aitoff"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAME TER["Central_Meridian",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Eas ting",EAST],AXIS["Northing",NORTH],AUTHO

Re: [postgis-users] improve query - summarize building count and area on each parcel

s p SET (building_count, building_fp) = (select count(*) as num, sum(st_area(b.geom)) as sumarea FROM buildings b on st_intersects(p.geom,st_centroid(b.geom) ) ; >>> Will check and report back Karsten From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On

Re: [postgis-users] improve query - summarize building count and area on each parcel

.0-17ubuntu1~20.04) 9.3.0, 64-bit | POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="120" GEOS="3.10.1-CAPI-1.16.0" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" Cheers Karsten

Re: [postgis-users] improve query - summarize building count and area on each parcel

That worked perfectly: Using just join and setting the index on the centroids geom as well I had the query finish in less than a minute (compared to three day running and not yet finished !) Does anyone have an explanation why 'regular' join is so much faster than 'left join'

[postgis-users] improve query - summarize building count and area on each parcel

(st_area(b.geom)) as sumarea FROM parcels p left join buildings b on st_intersects(p.geom,st_centroid(b.geom)) group by objectid ) subquery where p.objectid = subquery.objectid ; Any suggestions ? Cheers Karsten Vennemann ___ postgis-users mailing list

Re: [postgis-users] error: Directed line merging requires GEOS-3.11 or higher

) I think I asked before but I forget what you two answered. Thanks, Regina *From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Karsten Tebling *Sent:* Tuesday, December 20, 2022 6:07 AM *To:* postgis-users@lists.osgeo.org *Subject:* [postgis-users] error: Directed

[postgis-users] error: Directed line merging requires GEOS-3.11 or higher

Hello, I'm trying to restore a backup from a windows installation to a new linux mint 21 installation but it always fails with "error: Directed line merging requires GEOS-3.11 or higher". According to the postgis_full_version both installations should support geos 3.11, but I do get the error

Re: [postgis-users] HOw to connect to Mapserver with an https protocol

nd use a SSL certificate. One good option for a free one is Let's Encrypt see https://letsencrypt.org/ I hope this helps you to get started Cheers Karsten -- From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of mathias cunault Sent: Monday, September

Re: [postgis-users] CVE reported on log4j - is PostGIS affected?

I am by no way an expert - but since log4j is based on Java and Postgresql / PostGIS is not (instead based on C) there should be no way that there would be any issues related to Postgresl/postgis alone Anyone please let me know if I am wrong Karsten -Original Message- From: postgis

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

ed' installs, likely due to the bigger changes with proj in the newer versions ... Maybe I should just always use only Docker containers for such things to avoid such trouble ... Cheers Karsten _ From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Richa

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

Is anyone out there that happens to know how can I make PostgreSQL use the new proj lib version I have installed? -Original Message- From: karsten [mailto:kars...@terragis.net] Sent: Friday, October 15, 2021 12:03 To: 'PostGIS Users Discussion' Subject: RE: [postgis-users]

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

users-boun...@lists.osgeo.org] On Behalf Of karsten Sent: Friday, October 15, 2021 11:47 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection Yes will try to get this to work and report back here. The stack exchange issue mentioned

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

Yes will try to get this to work and report back here. The stack exchange issue mentioned that it worked with an older gdal version so will try with both different proj and gdal combinations Karsten -Original Message- From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

,AUTHORITY["EPSG","4326"]] as I hoped it would -Original Message- From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Paul Ramsey Sent: Friday, October 15, 2021 11:22 To: PostGIS Users Discussion Subject: Re: [postgis-users] Postg

Re: [postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

I found this related to the issue but no additional information apart from that https://gis.stackexchange.com/questions/396058/postgis-st-transform-fails-fo r-many-srid-on-ubuntu-20-04-2-lts _ From: karsten [mailto:kars...@terragis.net] Sent: Friday, October 15, 2021 11:12 To

[postgis-users] PostgreSQL 13, PostGIS 3.1 import - could not form projection

x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit postgis_version: 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 postgis_proj_version: 6.3.1 Cheers Karsten Karsten Vennemann <http://www.terragis.net> www.terragis.net ___

Re: [postgis-users] Searching city by coordinates

>>> Second question: which index should I create to speed these queries up? Just an Index on the column "way"? Yes that is the first and most important step. If 'way' is the geometry column then create a gist (spatial) index and also set a primary key ALTER TABLE cities.way ADD CONSTRAINT cit

Re: [postgis-users] Searching city by coordinates

2. well way is really the geometry column so CREATE INDEX cities_way_gist ON cities USING gist(way); ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Searching city by coordinates

sorry it is ALTER TABLE cities ADD CONSTRAINT cities_pkey PRIMARY KEY(id); CREATE INDEX cities_way_gist ON cities USING gist (geom); ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-us

Re: [postgis-users] Searching city by coordinates

ion St_Contains - it seems that if any point for the geometry is outside the other one the query will not return any results see https://postgis.net/docs/ST_Contains.html Cheers Karsten -Original Message- From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Luca B

Re: [postgis-users] Can PostgreSQL/PostGIS store and serve pictures andsounds?

/docs/current/largeobjects.html Cheers Karsten _ From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Shaozhong SHI Sent: Friday, November 06, 2020 00:31 To: PostGIS Users Discussion Subject: [postgis-users] Can PostgreSQL/PostGIS store and serve pictures

Re: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

wrong link - here it goes https://github.com/karstenv/dynamic_layers_qgis _ From: karsten [mailto:kars...@terragis.net] Sent: Thursday, September 24, 2020 15:29 To: 'PostGIS Users Discussion' Subject: RE: [postgis-users] Promoting PostgreSQL and PostGIS to wider business in

Re: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

vanced uses of plpython functions that enable cool dynamic data visualizations e.g. of dynamic weather data via PostGIS If anyone is interested there is a link to a video talk from QGIS NA 2020 with more details on there as well ... Cheers Karsten _ From: postgis-users [mailto:post

Re: [postgis-users] intersect two tables one row at a time

Yes that works thanks ! Would anyone have an explanation why the initial query below seems to intersect the entire two layers and not each records of table A with geometries of table B ? Cheers Karsten -Original Message- From: postgis-users [mailto:postgis-users-boun

[postgis-users] intersect two tables one row at a time

that I get back only the ids for each row (one geometry of table A at a time with the ids of intersecting polygons from table B) Cheers Karsten ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/po

Re: [postgis-users] ogr_fdw create data source connection error for directory containing XLSX files

running that via psql. Maybe not really pretty but it works - see below. Maybe I could try to wrap that into a custom psql function ... Cheers Karsten ##!/bin/bash fdwservername="myserver" exceltabname="raw_data" databasename="cmein" tempfile=&qu

[postgis-users] ogr_fdw create data source connection error for directory containing XLSX files

ce/geodata is readable by the postgres user ... But the query above gives me the following error : ERROR: unable to connect to data source "/var/xy/mapdata/source/geodata" SQL state: HV00N Is it even possible to connect via ogr_fdw to a

Re: [postgis-users] extending sides of parcels to cut/intersect roads before merge

y land use type. Karsten ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] extending sides of parcels to cut/intersect roads before merge

master/parcel_layer3.png ). Cheers Karsten ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS 2.02 view problem

('myloc_loc_gid_seq'::regclass) AS gid (and omitted the old one), still same error. what else should, itry - running out of ideas Cheers Karsten _ From: Richard Greenwood [mailto:richard.greenw...@gmail.com] Sent: Wednesday, July 24, 2013 07:05 AM To: kars...@terragis.net; Pos

[postgis-users] PostGIS 2.02 view problem

("geom"),'NDR& #39;). I have no idea why a count is needed here or where this field is coming from ...seems that the regular table have no issue with this. My VIEW public.geometry_columns shows these values for the view: "xy";"xy";"myloc";"g

[postgis-users] merging geometries of buffer on large data set

be running for 6 weeks or so :-) ? Any query examples or general insight are greatly appreciated . Cheers Karsten Karsten Vennemann Terra GIS Ltd 2119 Boyer Ave E Seattle, WA 98112 USA tel ++ 206 905 1711 fax ++ 925 905 1711

[postgis-users] create 10m buffer using input data in EPSG4326

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