Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?

2022-03-15 Thread Chris Tooley
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

2022-03-15 Thread Greg Troxel

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

2022-03-15 Thread Paul Ramsey



> 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

2022-03-15 Thread Shaozhong SHI
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