Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
Howdy! Pardon any mailing list _faux pas_ here, it's been a while. It was suggested on IRC that I detail my experience on the mailing list with the `spatial_sys_ref` table, specifically with this thread. Please forgive me if there is any incorrect terminology, I am not a GIS expert, I am but a humble software developer. We are using postgresql12 with postgis 3.2.1 I notice this is requested: > SELECT version() || ' ' || postgis_full_version(); > PostgreSQL 12.10 (Ubuntu 12.10-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="120" GEOS="3.9.1-CAPI-1.14.2" PROJ="6.3.1" GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER" I had to initially install a specific version of postgis (3.1.2) because it was requested by a company we are working with. We ended up installing 3.2 anyway because we encountered some other unrelated issues (maybe they're related! I'm not sure). This may be the reason we were having issues too. In any case, we were trying to convert from EPSG 3310 to 4326 using the following query (slightly edited) > select ST_Transform(geom, 4326) from data.table limit 1; and were encountering the following error: > ERROR: could not form projection (LWPROJ) from 'srid=3310' to 'srid=4326' This baffled me and results from searching on google weren't immediately helpful. I looked through many different pages and even looked at the epsg.io 3310 page (https://epsg.io/3310) to see if there was something I could glean from EPSG 3310 that might yield some results. As I said previously, I am not a GIS expert so that was semi-fruitless. I did notice at the bottom of epsg.io that there are a number of queries/etc for proj4 and other gis stuff, including a postGIS sql query I could run, which gave me some indication of how the transforms are handled, however, I didn't want to overwrite anything in the table because I figured the default stuff knows how to handle itself better than I. I ended up finding a comment in a gis stackexchange which talked about modifying the proj4text in the spatial_sys_ref table to solve their particular issue which sounded like what I was encountering, so I made sure I copied the original `proj4text` string and bit the bullet and modified the table with the following query: > update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34 +lat_2=40.5 +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-400 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs' where srid=3310; Lo and behold that seemed to allow the original failing query to work. I'm not entirely sure if it's actually transforming correctly, but at least the query isn't failing now! This is currently sufficient for our purposes regardless. Hopefully this helps someone else in the future! Future person who may be reading this, I salute your efforts! Sincerely, Chris ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] problem with points in polygon edge
Gustavo Martinez writes: > attached you can see an example of the situation. I am interested in > all the points. The arrow indicates one point that lays on the edge of > polygon 1 and polygon 2. Ah, now I understand that you are trying to use polygons that have overlapping edges and that you further want each point to be assigned to exactly one polygon. > If I use st_within(), that point is not counted in either polygon, but > if I use st_intersects() it is counted in both polygons. For that > reason, I wrote the function. > The data comes from a VMS device, that reports the GPS position, along > speed and vessel ID with a fixed frequency. > Hope it is more clear now. The question is then what you are really trying to do. If you are trying to count all the points in the union of some polygons, you can union the polygons and then count. If you want to know how many points are in each polygon, with a definition of "in" that says a point is in a polygon if it is really in it, or if it is on the boundary and the point is not in some other polygon sharing the boundary, however it is arbitrary which polygon the piont is considered to be in with the stipulation that polygons do not really overlap, but just touch at outer edges. You might consider a function that assigns points to polygons, perhaps taking the first member of selecting all polygons such that ST_Intersects(point, polygon) and assigning that in a table of points and assigned polygons, and then using that for your statistics. I guess you could also think about a function like ST_HalfOpenIntersect but that somehow treats each edge as either a closed or open interval depending on angle, so that points on shared edges would end up matching one and not the other. signature.asc Description: PGP signature ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Function question
> On Mar 14, 2022, at 11:40 PM, Simon SPDBA Greener wrote: > > Fellow PostGISers, > > I have two environments. > > select Version() >"PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-36), 64-bit" > select PostGIS_Full_Version() >"POSTGIS=""2.5.3 r17699"" [EXTENSION] PGSQL=""100"" > GEOS=""3.8.0-CAPI-1.13.1 "" PROJ=""Rel. 6.2.1, November 1st, 2019"" > GDAL=""GDAL 3.0.2, released 2019/10/28"" LIBXML=""2.9.1"" LIBJSON=""0.11"" > LIBPROTOBUF=""1.0.2"" RASTER" > > And. > > "PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit" > "POSTGIS=""3.1.2 3.1.2"" [EXTENSION] PGSQL=""130"" GEOS=""3.9.1-CAPI-1.14.1"" > SFCGAL=""1.3.8"" PROJ=""7.1.1"" LIBXML=""2.9.9"" LIBJSON=""0.12"" > LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" TOPOLOGY" > > I have written the following function: > > CREATE OR REPLACE FUNCTION spdba.STSquarifyLine(p_line geometry) > RETURNS geometry > LANGUAGE 'plpgsql' > IMMUTABLE > SECURITY DEFINER > AS > blah... > > The function does no calculations per se, it only swaps ordinates. > > PostGIS functions used are: > > ST_MakeLine / ST_SetSRID ST_MakePoint ST_X / ST_Y / ST_GeomFromEWKT > > The function does call another small function: > > create or replace function spdba.STGeneralDirection(p_geom geometry) > returns varchar(2) > LANGUAGE 'plpgsql' > IMMUTABLE > SECURITY DEFINER > as > (It uses Uses ST_BoundingRectangle ST_X ST_Y ST_StartPoint ST_EndPoint) > > This function returns the same result on both databases for a bunch of > different inputs. Happy that it works as I wish. > > However if I execute the STSquarifyLine function on the Linux box for a > particular input, I get what I consider to be the "right" result. When I > execute the same function with the same parameter value on the Windows box I > do not get the same result. (I have played around with IMMUTABLE and VOLATILE > but these make no difference.) > > I know this is a bit vague but any tips as to what may be going on? Still a little too vague. Like "different" as in 1.2341245129 and 1.2341245128 ? Or completely different? I think functions and data are required... ST_MakeLine takes whatever ordering you give it, so if you fed it directly from tables you might have different underlying ordering of inputs, and hence outputs. P. > > I am happy to supply the functions source (via email) if anyone is willing to > help. > > regards > > Simon > > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] The role of PostGIS in change management
I have always believed PostGIS and endeavour to support PostGIS community. Now, people ask about the role of PostGIS in change management. I would like to listen and drive change. Can anyone enlighten me? Regards, David ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users