Re: [postgis-users] Storage of tile offset on a raster dataset

[Pierre Racine]

James,

> When importing a large raster dataset, it is typically tiled and the only
> reference in postgis that these tiles form a single dataset is the
> raster_columns table.

You could also load your rasters with the -f option which add a column with the 
filename of the original raster file. You would then have a quick way to 
identify tiles being part of a specific raster.

> I'd like postgis to keep track of the tile offsets (from upper left to lower
> right) as I believe this should bring about performance improvements by
> reducing geometric operations to simple scaling laws. The tile offset could
> be stored as a linear index for simplicity.
> 
> Consider trying to locate which tile in a dataset a point lies within. You
> can calculate the tile offset in x and y with the following scaling law:

Computing the relation between a simple point and a tile might be easier like 
this but PostGIS is conceived to solve much harder problem like: Is this line 
or this polygon inside the tile? Do they touch, intersects or are completely 
contained by the tile? For that you need more complex algorithms. In your 
simple case, what if the tiles are rotated?

The usual query to find whether a point is inside a tile (and the raster value 
matching this point) goes like this:

SELECT ST_Value(rast, ST_SetSRID(St_MakePoint(170.1420703, -43.594937), 4326))
FROM raster_table
WHERE ST_Intersects(rast, ST_SetSRID(St_MakePoint(170.1420703, -43.594937), 
4326))

I'm not sure the math under ST_Intersects() and ST_Value() is much more complex 
than what you are proposing. And for sure ST_Intersects() is much more 
polyvalent. What if you have 100 tiles? Does your maths benefit from a 
spacial index? ST_Intersects() does... when there is one defined.

>- Checking alignment of tiles. This is especially important when loading
> multiple datasets which should be aligned. Lets say I load 1 'base' dataset
> which gets tiled on the way in. Any subsequent tiles I load (e.g. to update
> a region of the dataset) need to be aligned. I can check that my input tile
> has the same corner coordinates of the tile it is replacing by calculating
> the tile index and pulling out the relevant information.

You can already compare raster alignment with ST_SameAlignment() and realign a 
raster with ST_Resample(), ST_Rescale(), ST_Reskew() and ST_SnapToGrid().

> - Iterating a raster dataset. Especially important when the processing
> you are doing is order-dependent (such as sequential neighbourhood
> searches
> (for e.g. terrain processing,  or imposing boundary conditions)

To find the neighbours of a tile you can intersect them with a small buffer 
around the tile of interest.

To return them always in the same sequence you can SORT them BY 
ST_UpperleftX(), ST_UpperleftY().

> To my mind, this seems quicker than the 'normal' method of doing
> something

You will have to demonstrate this affirmation... I think a lot of people would 
be amazed if you get something quicker than the actual PostGIS set of tools.

> Are there any facilities in postgis to support this already? Or am I
> overlooking some concepts about postgis that mean the above is
> unnecessary
> (i.e. the use cases can be met with no performance hit)

I think you underestimate the power of the PostgreSQL GiST index...

Good luck!

Pierre
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Error on a raster table restore

[Cedric Duprez]

Yes, the function ST_BandMetadata, with the right parameters, is in my public 
functions list.
Both full versions of postgis and postgresql are the same.

Le 15/01/2016 10:21, Tom van Tilburg a écrit :
Just checking the obvious:
Is there a function ST_BandMetedata in your functions list?
And what is the full version output of the original server?


On Fri, 15 Jan 2016 at 10:03 Cedric Duprez 
<cedric.dup...@ign.fr>
 wrote:
Hi Tom,

Thanks for your answer.
I am sure that rasters are enabled in my postgis dbase (the first thing
I checked). Raster functions are in the public schema.
This is the result of SELECT postgis_full_version() :
POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6
March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1"
LIBJSON="UNKNOWN" TOPOLOGY RASTER

Cedric

Le 15/01/2016 09:09, Tom van Tilburg a écrit :
>
> Cedric,
>
> Are you sure rasters are enabled in your new postgis dbase? Please
> check for the existence  of raster functions (st_bandmetadata in
> particular) in public.functions and run SELECT postgis_full_version()
>
> Tom
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] CREATE EXTENSION postgis fails with "ERROR: could not load library "/usr/pgsql-9.5/lib/postgis-2.2.so": /usr/pgsql-9.5/lib/postgis-2.2.so: undefined symbol: GEOSClipByRect"

[Matthew Baker]

For what it's worth, the Ubuntu/Debian install was causing this same 
problem from the PostgreSQL APT repository


What was happening was when installing the postgresql-contrib package, 
it was defaulting to the 9.5 version of the package, and so was 
installing another instance of PostgreSQL - so versions 9.4 and 9.5 were 
starting up.


So when I tried to install the postgis extension, it was giving a 
similar error - I believe because it was trying to create the extension 
in the 9.5 version of PG, while PGIS was installed in the 9.4 version


By forcing the version of postgresql-contrib to 9.4, it illeviated the 
problem:


sudo apt-get install postgresql-9.4-postgis-2.1 pgadmin3*postgresql-contrib-9.4*

Not sure if there is an equivalent problem going on...

(I've updated the Ubuntu/Debian instructions here:)

https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt

-m



On 1/15/2016 7:35 AM, Paul Ramsey wrote:

Thanks for confirming, I've notified the pgdg packager.
P

On Fri, Jan 15, 2016 at 6:25 AM, Paul Edwards  wrote:

Sorry for the spam, but as you suspected:

  yum deplist postgis2_95
bunch of dependencies
dependency: geos >= 3.4.2


On Friday, January 15, 2016 at 9:20:33 AM UTC-5, Paul Edwards wrote:

Was debugging the exact issue yesterday. Updating GEOS to 3.5 did the
trick for me. This is the version of geos provided for in the pgdg95 repo.



On Thursday, January 14, 2016 at 9:13:13 AM UTC-5, Paul Ramsey wrote:

Check the versions of the GEOS rpm you have installed, and also check
the "requires" declarations for the postgis rpm. Probably you'll find
the GEOS rpm is version 3.4 and that the postgis rpm doesn't declare a
dependency for geos 3.5. See if you can track down a GEOS 3.5 rpm.

P

On Thu, Jan 14, 2016 at 2:04 AM, Moti Umansky  wrote:

I have reproduced the issue when installing with yum install from the
internet.

I think there is something wrong with the last version installation on
Oracle Linux (or Red Hat).

Has anyone succeeded to install the last version on Oracle Linux or Red
Hat?



Thanks,

Moti





From: Moti Umansky
Sent: יום א 10 ינואר 2016 18:08
To: 'postgi...@lists.osgeo.org'
Subject: CREATE EXTENSION postgis fails with "ERROR: could not load
library
"/usr/pgsql-9.5/lib/postgis-2.2.so": /usr/pgsql-9.5/lib/postgis-2.2.so:
undefined symbol: GEOSClipByRect"



Hi



I'm trying to install the new 9.5 Postgres version with Postgis 2.2
(downloaded from

https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6.7-x86_64/)



When I issue "CREATE EXTENSION postgis", I'm getting :



ERROR:  could not load library "/usr/pgsql-9.5/lib/postgis-2.2.so":
/usr/pgsql-9.5/lib/postgis-2.2.so: undefined symbol: GEOSClipByRect



OS : Oracle Linux 6U7

Postgres version : "PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled
by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit"



I have tried to reproduce the issue on Postgres 9.4.5 downloaded from

https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6.7-x86_64.



The issue was not reproduced.



Thanks,

Moti


Powered By Office365

The information contained in this communication is intended solely for
the
use of the individual or entity to whom it is addressed and others
authorized to receive it.
It may contain confidential or legally privileged information.
If you are not the intended recipient you are hereby notified that any
disclosure, copying, distribution or taking any action in reliance on
the
contents of this information is strictly prohibited and may be
unlawful.
If you have received this communication in error, please notify us
immediately by forwarding this email to mail...@ness.com and then
delete
it from your system.
Ness technologies is neither liable for the proper and complete
transmission
of the information contained in this communication nor for any delay in
its
receipt.

___
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] How to cast geometry to 'varbinary' in PostGIS / PostgreSQL

[Matthew Baker]

Hi all,

Does anyone have any thoughts to the problem below?

In MSSQL Server, casting geometry to varbinary(max) produces a string 
that looks like this:


|0x3D0B010CA0F3BE3B5CE9474100EC414E3AF93941|

When you insert that string into a geometry column, a geometry object is 
built.


However, I can't seem to find out what the equivalent data format is in 
PostGIS / PostgreSQL in order to bring that same varbinary format out of 
a PostGIS geometry column (for the purpose of loading it into an MSSQL 
Server Geometry object).


I've read that the 'bytea' format does the same as varbinary, but the 
bytea version of a PostGIS geometry object looks like this:


|\001\001\000\000\346\020\000\000\263\234\021o\224?Z\300-\265\373^\322\334C@|

Is there a way to get the same string that the MSSQL varbinary format 
produces from PostGIS / PostgreSQL?


Some background into this: I'm developing my own ETL tools using Python...

*psycopg2* works well with PostgreSQL (PostGIS) database conversion - 
and PostgreSQL casts geometry to varchar in a SQL statement in my python 
script, which inserts nicely back into a PostGIS geometry column - 
PostGIS ETL done!


*pypyodbc* is working great for the receiving end of a psycopg2 query, 
but as the problem above states, I can't find the correct format to cast 
the PostGIS geometry to in the SQL in the python script... otherwise, I 
can read other columns from PostgreSQL and write them to MSSQL just fine.


I've posted this to GIS.SE too... (hope that's ok...)

http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql

Thanks in advance for any thoughts or suggestions!

-Matt Baker
Denver Public Schools
Denver, CO
mattba...@gmail.com



___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] How to cast geometry to 'varbinary' in PostGIS / PostgreSQL

[Paul Ramsey]

Hi Matt,

varbinary and bytes are the "same" insofar as they hold an array of
bytes. there's no guarantee though, that geometry->varbinary in
sqlserver going to produce the same thing as geometry->bytes in
postgresql.

As a for-example, the sqlserver varbinary example you show about
doesn't start with 00 or 01. That means it's not valid WKB (which
start with a zero or one byte to flag the endianness). It's "something
else". This could crimp your style.

0x3D0B 010C A0F3BE3B5CE9474100EC414E3AF93941

It looks like it might end with two doubles, so perhaps it's a point?
That should be WKB type 01, but I don't see a 0001 anywhere, which
is what the WKB type number would look like (int32 type)

Anyways, your best bet is going to be going via WKB, so maybe work on
wrapping your PostGIS calls in ST_AsBinary or ST_AsEWKB (for 3d/4d
objects). To get hex encoded text versions, just wrap the bytes
outputs in an 'encode' function, like

select encode(st_asbinary(geom), 'hex') from my_table

ATB,

P



On Wed, Jan 20, 2016 at 12:17 PM, Matthew Baker  wrote:
> Hi all,
>
> Does anyone have any thoughts to the problem below?
>
> In MSSQL Server, casting geometry to varbinary(max) produces a string that
> looks like this:
>
> 0x3D0B010CA0F3BE3B5CE9474100EC414E3AF93941
>
> When you insert that string into a geometry column, a geometry object is
> built.
>
> However, I can't seem to find out what the equivalent data format is in
> PostGIS / PostgreSQL in order to bring that same varbinary format out of a
> PostGIS geometry column (for the purpose of loading it into an MSSQL Server
> Geometry object).
>
> I've read that the 'bytea' format does the same as varbinary, but the bytea
> version of a PostGIS geometry object looks like this:
>
> \001\001\000\000 \346\020\000\000\263\234\021o\224?Z\300-\265\373^\322\334C@
>
> Is there a way to get the same string that the MSSQL varbinary format
> produces from PostGIS / PostgreSQL?
>
> Some background into this: I'm developing my own ETL tools using Python...
>
> psycopg2 works well with PostgreSQL (PostGIS) database conversion - and
> PostgreSQL casts geometry to varchar in a SQL statement in my python script,
> which inserts nicely back into a PostGIS geometry column - PostGIS ETL done!
>
> pypyodbc is working great for the receiving end of a psycopg2 query, but as
> the problem above states, I can't find the correct format to cast the
> PostGIS geometry to in the SQL in the python script... otherwise, I can read
> other columns from PostgreSQL and write them to MSSQL just fine.
>
> I've posted this to GIS.SE too... (hope that's ok...)
>
> http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql
>
> Thanks in advance for any thoughts or suggestions!
>
> -Matt Baker
> Denver Public Schools
> Denver, CO
> mattba...@gmail.com
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Geoprocessing & BigData

[Ravi Pavuluri]

Felix,Thanks for the tip and  I am kind of doing the same way. An intersect or 
union would help me test and compare my result with other GIS software. Also, 
thanks for the mapbutcher link on union. I will look into it.

Steve, 
Thanks for the tiles approach, I am going to to give it a try. 

Pierre,Thanks for the postgis addon link. I will look into it.
-Ravi.
 

On Tuesday, January 19, 2016 10:30 AM, Pierre Racine 
 wrote:
 

 > @Vincent : For ArcGIS Union, please see here.
> http://resources.esri.com/help/9.3/arcgisengine/java/gp_toolref/analysis_
> tools/union_analysis_.htm

ArcGIS Union resume to removing overlaps from the union of two tables. Look at 
the ST_DifferenceAgg() example in the PostGIS Addons:

https://github.com/pedrogit/postgisaddons/blob/master/postgis_addons.sql

This  produce a clean table (layer) without overlaps from the union of two 
(polygon) tables (layers).

Pierre


  ___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] SUM: How to cast geometry to 'varbinary' in PostGIS / PostgreSQL

[Matthew Baker]

So the path I took was, as suggested by Paul (and I assumed I would have 
to anyway) was to:


- Select the geometry as ST_AsText from the PG source table
- create a geom_temp column of varchar(max) type in the MSSQL 
destination table

- dump the geometry text into geom_temp column in MSSQL
- When the ETL is done, use STGeomFromText to update the MSSQL geometry 
column

- delete the geom_temp field
- done!

Again all in python using psycopg2 (postgres) and pypyodbc (mssql server)

Oh and any spatial function in PostGIS can be used to transform the data 
(ie. for us, re-project to State Plane for our MSSQL clients) on the fly...


So this is the best spatial ETL solution I've seen so far - no more 
meddling with Talend or GeoKettle... and no more FME either! (Although 
my needs are small, the MSSQL portion was a thorn in my side...)


Thanks again!!!

-m



On 1/20/2016 1:52 PM, Paul Ramsey wrote:

Hi Matt,

varbinary and bytes are the "same" insofar as they hold an array of
bytes. there's no guarantee though, that geometry->varbinary in
sqlserver going to produce the same thing as geometry->bytes in
postgresql.

As a for-example, the sqlserver varbinary example you show about
doesn't start with 00 or 01. That means it's not valid WKB (which
start with a zero or one byte to flag the endianness). It's "something
else". This could crimp your style.

0x3D0B 010C A0F3BE3B5CE9474100EC414E3AF93941

It looks like it might end with two doubles, so perhaps it's a point?
That should be WKB type 01, but I don't see a 0001 anywhere, which
is what the WKB type number would look like (int32 type)

Anyways, your best bet is going to be going via WKB, so maybe work on
wrapping your PostGIS calls in ST_AsBinary or ST_AsEWKB (for 3d/4d
objects). To get hex encoded text versions, just wrap the bytes
outputs in an 'encode' function, like

select encode(st_asbinary(geom), 'hex') from my_table

ATB,

P



On Wed, Jan 20, 2016 at 12:17 PM, Matthew Baker  wrote:

Hi all,

Does anyone have any thoughts to the problem below?

In MSSQL Server, casting geometry to varbinary(max) produces a string that
looks like this:

0x3D0B010CA0F3BE3B5CE9474100EC414E3AF93941

When you insert that string into a geometry column, a geometry object is
built.

However, I can't seem to find out what the equivalent data format is in
PostGIS / PostgreSQL in order to bring that same varbinary format out of a
PostGIS geometry column (for the purpose of loading it into an MSSQL Server
Geometry object).

I've read that the 'bytea' format does the same as varbinary, but the bytea
version of a PostGIS geometry object looks like this:

\001\001\000\000 \346\020\000\000\263\234\021o\224?Z\300-\265\373^\322\334C@

Is there a way to get the same string that the MSSQL varbinary format
produces from PostGIS / PostgreSQL?

Some background into this: I'm developing my own ETL tools using Python...

psycopg2 works well with PostgreSQL (PostGIS) database conversion - and
PostgreSQL casts geometry to varchar in a SQL statement in my python script,
which inserts nicely back into a PostGIS geometry column - PostGIS ETL done!

pypyodbc is working great for the receiving end of a psycopg2 query, but as
the problem above states, I can't find the correct format to cast the
PostGIS geometry to in the SQL in the python script... otherwise, I can read
other columns from PostgreSQL and write them to MSSQL just fine.

I've posted this to GIS.SE too... (hope that's ok...)

http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql

Thanks in advance for any thoughts or suggestions!

-Matt Baker
Denver Public Schools
Denver, CO
mattba...@gmail.com




___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users