Dear list members,
I have a table with 35 billion rows containing point geometries (SRID 31467)
and use it for intersections with multiple polygon datasets. To improve
performance I would like to use declarative partitioning to split the point
table into sensible table partitions. Because of t
Hi Jesper,
just from the manual: If you have the value, could you not use ST_PixelOfValue
to extract the location?
from the PostGIS-manual:
ST_PixelOfValue — Get the columnx, rowy coordinates of the pixel whose value
equals the search value.
Regards,
Birgit
Von: "Jesper Kempe"
BQ_BEGIN
On Aug 13, 2019, at 7:56 AM, Birgit Laggner < [
mailto:birgit.lagg...@thuenen.de | birgit.lagg...@thuenen.de ] > wrote:
Dear list,
on our test server, we currently try to test how to upgrade our
PostgreSQL/PostGIS database. Sadly, we are stuck somewhere and would be very
g
Dear list,
on our test server, we currently try to test how to upgrade our
PostgreSQL/PostGIS database. Sadly, we are stuck somewhere and would be very
glad if anybody could help.
Our test server is running on an Ubuntu 16.04 system in a virtual machine. We
are coming from PostgreSQL 9.5.17
Hi Simon,
I assume, you are trying to get this all done in one query, right? Did you try
to throw ST_MakeValid into the mix? Like:
ST_Transform(ST_Union(ST_MakeValid(ST_Transform(a.geog4326::geometry, 3857))),
4326)::geography
Regards,
Birgit
Von: "Simon Greener"
An: "PostGIS Users D
Hi Regina,
I work at a Federal Research Institute and we use PostGIS for various purposes.
A very common use case is joining different spatial datasets (vector and
sometimes raster) with ST_Intersects, e.g. to find out how agricultural land
use is related to nature conservation areas or other p
Hi,
I believe QGIS can not deal with GeometryCollections which are the resulting
geometrytype of ST_Split.
Your idea is not bad, but why do you save the result as wkt?
I would try:
SELECT (ST_Dump(ST_Split(circle, line))).geom::geometry (polygon,0) As geom
INTO layerq
FROM (SELECT
ST_Mak
a specific interest to go to the bottom of the
problem?
Thanks a lot again for the idea with the wrapper to extract to problematic
rows.
Kind regards,
Birgit
Von: "Birgit Laggner"
An: "PostGIS Users Discussion"
Gesendet: Mittwoch, 19. September 2018 09:47:4
others
then
return true ;
end ;
end ;
$$
language plpgsql ;
вт, 18 сент. 2018 г. в 16:25, Birgit Laggner < [
mailto:birgit.lagg...@thuenen.de | birgit.lagg...@thuenen.de ] >:
Dear discussion group,
I am trying to ST_Union several polygons and get a TopologyException
(GEOS
Dear discussion group,
I am trying to ST_Union several polygons and get a TopologyException
(GEOSUnaryUnion: TopologyException: found non-noded intersection between
LINESTRING (3.56442e+06 5.42679e+06, 3.56442e+06 5.42679e+06) and LINESTRING
(3.56442e+06 5.42679e+06, 3.56442e+06 5.42679e+06) a
Hi James,
perhaps you could split the data per multipolygon into the largest polygon and
the rest. Then buffer the smaller polygons by a small amount and union them
with the large polygon?
Regards,
Birgit
Von: "James Sewell"
An: postgis-users@lists.osgeo.org
Gesendet: Montag, 6. August
Hi Jonathan,
I tested your example and it seems the reason why the ST_Union results in an
empty polygon is because the resulting polygons of the ST_Buffer are already
empty. I could imagine complex polygons don't go well with a very small buffer.
In my experience, ST_Union only results in emp
I am glad I could help :-)
Regards, Birgit
Am 04.11.2016 um 15:45 schrieb Stephen Crawford:
I had to make just a few minor adjustments, only about casting types
which you couldn't have know about anyway. Worked well. Many Thanks.
On 11/3/2016 8:20 AM, Birgit Laggner wrote:
Hi St
Hi Stephen,
here my proposed solution (untested!!)
DO $$
DECLARE r record; doy date;
BEGIN
FOR r IN (SELECT DISTINCT ST_Value(rast,x,y) AS obs_date FROM
doy_raster, generate_series(1,(SELECT ST_Width(rast) FROM doy_raster))
AS x, generate_series(1,(SELECT ST_Height(rast) FROM doy_raster)) AS
point. However, the endpoint of one line could
break into 2 new starpoints.
thanks,
jon
On Tue, Sep 20, 2016 at 5:38 AM Birgit Laggner
mailto:birgit.lagg...@thuenen.de>> wrote:
Hi Jonatan,
based on the anonymous code block Leknín came up with, I tried to
adapt it to your probl
ry[];
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;
geom := ST_ForceCollection(ST_Collect(c_geom));
EXIT WHEN c IS NULL;
END LOOP;
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||
Hi Paolo,
here an example how it could work (mind that you have to replace the
??). You could also recode the DO block into a function if you would
like that better.
DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar;
output_table varchar; r record; gid integer; geom geome
Hi Jonatan,
based on the anonymous code block Leknín came up with, I tried to adapt
it to your problem:
DO $$
DECLARE c_line record; c_gid integer; c_geom geometry; line record; gid
integer; geom geometry; i integer; n integer;
BEGIN
EXECUTE 'SELECT count(*) FROM electric_line' INTO n;
--i
Birgit
Am 16.06.2016 um 10:34 schrieb Sandro Santilli:
On Wed, Jun 15, 2016 at 07:19:34PM +0200, Birgit Laggner, vTI wrote:
Hi Sandro,
yes, I read so, too. But, our database has PostGIS version:
"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel.
com/Remi-C/_utilities/blob/master/postgis/rc_split_multi.sql
Cheers,
Rémi-C
2016-06-15 19:19 GMT+02:00 Birgit Laggner, vTI
mailto:birgit.lagg...@thuenen.de>>:
Hi Sandro,
yes, I read so, too. But, our database has PostGIS version:
"POSTGIS="2.2.1 r14555" GEOS
9" TOPOLOGY RASTER" and I still get that error. Any idea why?
Regards,
Birgit
Am 15.06.2016 um 19:02 schrieb Sandro Santilli:
On Tue, Jun 14, 2016 at 02:52:52PM +0200, Birgit Laggner wrote:
ERROR: Splitting a Polygon by a MultiLineString is unsuppor
hrem Adressbuch!
Notice: Thünen Institute has changed its domain.
Please change my email address in your address book!
Dipl.-Geoökol. Birgit Laggner
Thünen-Institut für Ländliche Räume / Thünen Institute of Rural Studies
Bunde
OS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.1,
04 March 2015" GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.2"
LIBJSON="0.11.99" TOPOLOGY RASTER". So, I would suppose I shouldn't have
this problem.
Any other ideas?
Thanks and rega
Birgit
Am 15.06.2016 um 09:24 schrieb Marcin Mionskowski:
On Tue, 14 Jun 2016 15:37:40 +0200, Marcone
wrote:
2016-06-14 9:52 GMT-03:00 Birgit Laggner :
I would like to use the ST_Split function to split polygons from one
table with all intersecting lines from another table. Unfortunately,
Dear list,
I would like to use the ST_Split function to split polygons from one
table with all intersecting lines from another table. Unfortunately, I
get the following error:
ERROR: Splitting a Polygon by a MultiLineString is unsupported
SQL Status:XX000
This is my query:
select betr_id, (
Hi Chris,
for the warning if a user is within a mile of the warned area, you could
use ST_DWithin instead of ST_Within.
Since you have EPSG 4326, I would convert the geometries into geography
types because then you can define the distance in meters (not in
degrees), perhaps like:
ST_DWithi
Hi Pietro,
you try to grant permissions to user catasto_admins - maybe the problem
lies here? You don't write about permission settings for catasto_admins,
so I can't be sure.
Regards,
Birgit
Am 11.05.2016 um 12:39 schrieb Pietro Rossin:
Hello
I have problems with postgres/postgis query/vi
Dear list,
is there anybody who can tell me if the algorithm Average is available
for Raster Resampling? It is not listed as an algorithm in the raster
reference of the PostGIS manual but somehow I am hoping it might still
be implemented :-) We are using PostGIS version 2.2.1
(postgis_full_ve
Hi Lars,
awesome, thank you. I will surely test your code at the next occasion.
Regards,
Birgit
Am 10.02.2016 um 09:11 schrieb Lars Aksel Opsahl:
Hi
There has been different mails about this topic lately. We have now have added
the code we use to Github and hopefully somebody can pick up s
Hi Alain,
your approach sounds good to me. I used a similar approach some time
ago. As for the creation of the bigger polygon during the 2nd step, with
my older PostGIS version, I sometimes came across topology exceptions
using the st_union function for this. Maybe this is not a problem
anymo
You don't have to use postgres user per se - but the user using the
'copy' command has to be a superuser. Perhaps it might be easier to
change your own user role accordingly?
Am 22.12.2015 um 14:25 schrieb Paolo Cavallini:
Il 22/12/2015 14:21, Humberto Cereser Ibanez ha scritto:
Hi Paolo,
If
Hi Paolo,
perhaps converting into ewkt or ewkb format with st_asewkt() or
st_asewkb() could be a workaround? I haven't worked with geometries
including m-coordinates yet, so this is only an idea...
Good luck and regards,
Birgit
Am 22.12.2015 um 13:27 schrieb Paolo Cavallini:
Hi all,
I disc
the necessary values but it seems progress is being made.
I have a book coming on SQL – I clearly need to do my homework more
thoroughly J
I will confirm when it stops!
Best wishes
Darrel
*From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org]
*On Behalf Of *Birgit Laggner
Hi Darrel,
my PostGIS version is too old for testing, but if I read the
documentation right, then your expression has to be SQL. And IF THEN
ELSE etc. is not SQL as far as I know - SQL has CASE WHEN.
So, I would assume, you would need to write your expression like this:
'CASE WHEN [rast2] >
Hi Bob,
I had the problem sometimes that st_union() result was NULL. Perhaps
that's the case with your query, too? Is it necessary to use st_union()?
Otherwise you could replace it with st_collect(), if you just want to
collect all geometries into a multilinestring. That's more robust
because
;
set @v = '''1''';
SELECT * from gradeschools_aroo where gradecode = @v;
SELECT * from gradeschools_aroo where gradecode = ''1''
ERROR: syntax error at or near "1"
-Original Message-
From: Birgit Laggner [ma
1. Using string variables with PGScript (Bistrais, Bob)
2. Re: Using string variables with PGScript (David Fawcett)
3. Re: Using string variables with PGScript (Birgit Laggner)
--
Message: 1
Date: Wed, 23
Hi Bob,
I would recommend something as simple as
SELECT * from mytable where myfield = '@MYSTRING';
I tried using a string variable in a PGScript once, too, and if I
remember correctly, quote_literal() did not work.
Good luck and regards,
Birgit
Am 24.09.2015 um 03:43 schrieb David Fawc
Hi Joseph,
I tried your intersects query after creating the multipolygon using the
st_geomfromtext function with the coordinates from your geojson objects.
There were no problems with the intersects query. The query returned a
result even with the point within the lower polygon.
So my questi
Hi Trang,
I think, it could help to create btree indices on "startts" and "uuid",
too, since you are using them in your where clause as a filter (a
probably unnecessary question regarding your date filter: I would expect
the result of "t.startts > '2015-01-16' and t.startts < '2015-01-17'" to
(I would love it if you can prove me wrong.) Note that the SDE
functionality is now built into all of ESRI's products, and the SDE
server software will be depricated after 10.2.
On Thu, Oct 2, 2014 at 2:30 AM, Birgit Laggner
mailto:birgit.lagg...@ti.bund.de>> wrote:
We use Ar
Yes, that's true: ArcGIS is not able to read the PostGIS raster data type.
Am 02.10.2014 16:20, schrieb David Haynes:
I have not been able to use Arc 10.3 for visualizing raster data
stored in postgresql
On Thu, Oct 2, 2014 at 2:30 AM, Birgit Laggner
mailto:birgit.lagg...@ti.bund.de>
We use ArcGIS 10.2 for read/write interaction with our PostgreSQL
database. We only have ArcGIS Desktop without SDE. There is a driver
(database client file) from ArcGIS for PostgreSQL, you have to install.
We are more or less content with this concept. Surely, the performance
is not as if you
Hi Adam,
perhaps you could solve the problem with this workaround:
1. Transform pt1 into geometry data type with srid 4326 (coordinates are
in degrees, now)
2. Create a new point by shifting pt1 for # degrees in x-direction
3. Create a linestring geometry by connecting pt1 and the new point
4.
Hi L.
I would suspect the first assignment of the result_pnt (result_pnt :=
ST_Collect(result_pnt, st_pointn(lineusp, point_id));) as the cause of
the problem: Within your assignment, you try to use the geometry of
result_pnt which does not yet exist at this moment. Maybe, you could
work with
Hi,
we work with ArcGIS 10.1, now, and since, we don't need ArcSDE for
communication with PostgreSQL db and PostGIS geometries. If you install
special db client libraries for PostgreSQL, you are able to create a
direct database connection to the PostgreSQL database and to directly
im- and exp
46 matches
Mail list logo
