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

[David Haynes]

The problem is with pg_restore for raster tables not in the public schema
use this url for reference. https://trac.osgeo.org/postgis/ticket/2485#no1

This is a fix I have implemented that seems to work, I might have all of
the raster_constraint functions though...
SHOW search_path'

Alter the following functions to include the pg_catalog in the search path.
Alter Function _raster_constraint_nodata_values (raster) SET
Search_path="$user", pg_catalog,public;
Alter Function _raster_constraint_out_db (raster) SET Search_path="$user",
pg_catalog,public;
Alter Function _raster_constraint_pixel_types(raster) SET
Search_path="$user", pg_catalog,public;
Alter Function _overview_constraint(raster, integer, name, name, name)  SET
Search_path="$user", pg_catalog,public;

now try restore command



On Wed, Jan 20, 2016 at 10:35 AM, Cedric Duprez 
wrote:

> 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 
> listpostgis-users@lists.osgeo.orghttp://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] tiger geocoder address normalization issue (bug?)

[Stephen Woodbridge]

Hi James,

Thanks for reporting this. I'm hoping to have time this spring to work 
with Regina to do a major rewrite of the geocoder. The existing code is 
very Tiger centric and Tiger data has a lot of oddities in it some of 
which are built into the the existing code.


The address standardizer is a new facility that was added last year and 
I have used it to build a few geocoders that are more data agnostic. I 
have just started working on a new address standardizer written in C++ 
that I hope will be easier to understand and maintain and it already has 
support for UTF8 data so it will support European language addressing.


For the curious or those that might want to help, you can checkout:
https://github.com/woodbri/address-standardizer
https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md

So what does this mean with regards to the report? Well things are 
changing and hopefully the bad stuff is going away to be replaced by a 
better more generic solution that will have a new set of issues, but 
hopefully a small set of them and ones that are easier to fix.


-Steve
 http://imaptools.com

On 1/28/2016 4:39 PM, James Marca wrote:

Hi,

My goal is to use the address standarizer to match streets from two
different data sources.

Test case is 8th Street in Alameda CA

I was reading through the docs and found standardize_address
(http://postgis.net/docs/standardize_address.html), along with two
different ways to invoke it...using the us_[lex,gaz,rules] and the
tiger.pagc[lex,gaz,rules].

I think I found an issue of sorts.

First, using the 'us_' variant, all is well.  If I query with Eighth
Street or 8th Street, I expect the street name to be the same so I can
match them, and they are:

hpms_geocode=# SELECT name,suftype FROM standardize_address('us_lex', 'us_gaz', 
'us_rules', '1 8th St, Alameda, CA');
  name | suftype
--+-
  8| STREET
(1 row)

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
8th St, Alameda, CA');
  name | suftype
--+-
  8| ST
(1 row)


However, if I do the same with the tiger.pagc variant, the results do
not match:

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
8th St, Alameda, CA');
  name | suftype
--+-
  8| ST
(1 row)

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
Eighth St, Alameda, CA');
  name | suftype
--+-
  8TH  | ST
(1 row)


Obviously I will just use the 'us_' version, but if the 'tiger.pagc_'
version is maintained, I think this is a bug and should be fixed

Regards,
James





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




---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

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

[postgis-users] tiger geocoder address normalization issue (bug?)

[James Marca]

Hi,

My goal is to use the address standarizer to match streets from two
different data sources.

Test case is 8th Street in Alameda CA

I was reading through the docs and found standardize_address
(http://postgis.net/docs/standardize_address.html), along with two
different ways to invoke it...using the us_[lex,gaz,rules] and the
tiger.pagc[lex,gaz,rules].

I think I found an issue of sorts.

First, using the 'us_' variant, all is well.  If I query with Eighth
Street or 8th Street, I expect the street name to be the same so I can
match them, and they are:

hpms_geocode=# SELECT name,suftype FROM standardize_address('us_lex', 'us_gaz', 
'us_rules', '1 8th St, Alameda, CA');
 name | suftype 
--+-
 8| STREET
(1 row)

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
8th St, Alameda, CA');   
 name | suftype 
--+-
 8| ST
(1 row)


However, if I do the same with the tiger.pagc variant, the results do
not match:

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
8th St, Alameda, CA');
 name | suftype 
--+-
 8| ST
(1 row)

hpms_geocode=# SELECT name,suftype FROM 
standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' , '1 
Eighth St, Alameda, CA');
 name | suftype 
--+-
 8TH  | ST
(1 row)


Obviously I will just use the 'us_' version, but if the 'tiger.pagc_'
version is maintained, I think this is a bug and should be fixed

Regards,
James





pgpmPiqZAZRH6.pgp
Description: PGP signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] The install of the last version of postgis on RedHat 7U1 fails with "Error: Package: SFCGAL-libs-1.2.2-1.rhel7.x86_64 (Postgres_9.5) Requires: libboost_date_time-mt.so.1.53.0()(64b

[Devrim Gündüz]

Hi,

Ouch. Will look.

Regards, Devrim

On January 28, 2016 8:11:07 AM GMT+02:00, Moti Umansky  
wrote:
>When running "yum install postgis2_95" are getting :
>
>...
>Error: Package: SFCGAL-libs-1.2.2-1.rhel7.x86_64 (Postgres_9.5)
>   Requires: libboost_date_time-mt.so.1.53.0()(64bit)
>...
>
>We could not find which RPM contains 
>libboost_date_time-mt.so.1.53.0()(64bit).
>
>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
>mailad...@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
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Geoprocessing & BigData

[David Haynes]

The way that were are thinking of handling something like that now would
load the partitioned data into nodes. I imagine you want to load all the
data into 1 table on 1 node.
This would result in a large table that might be difficult for simple
queries, alternatively you could use the postgresql inheritance and load
data and create indices by state.

Address the comment that Remi-C brought up.
I have spent a lot of time investigating Postgres-Xc / Postgres-XL /
CitusDB and greenplum. None of them really parallelize your spatial
functions. This is what we have found.

The current implementation of Paragon uses a variant of round-robin
declustering. The declustering algorithm produced 1024 spatial partitions
after processing the dataset in Table 1. The physical storage and
management of the partitions in Paragon is done by taking advantage of
PostgreSQL’s sharding feature [16]. We extended the SQL create table
statement to specify spatial declustering parameters, such as, the number
of partitions to be created, the declustering method, and a label for the
declustering scheme. To execute a spatial join, the labels of the two
tables, being joined, must match. This mechanism allows the same spatial
dataset to be partitioned using different declustering schemes.

Table 1. Spatial data used for comparison

*Database Table (acronym)*

*Geometry*

*Number of Objects*

Area-water (Aw)

Polygon

39,334

Area-landmass (AI)

Polygon

5,5951

Edge (Ed)

Polyline

4,173,498



Table 2. Comparison of Query Times: Paragon vs PostgreSQL

*Query (acronym)*

*PostgreSQL (seconds)*

*Paragon (seconds)*

*Speedup*

Polygon overlaps Polygon (Aw_ov_Aw)

  77.3

  53.5

 1.37

Polyline Touches Polygon (ED_to_Al)

452.9

246.0

 1.84

Polyline Crosses Polyline (Ed_cr_Ed)

  1693.2

  1022.0

 1.65



We executed spatial join queries from the Jackpine spatial database
benchmark [6] with Paragon in a two node cluster. The queries are expressed
in SQL with some of the spatial predicates adopted by Open Geospatial
Consortium (OGC). For instance, Code 1 demonstrates the “Polyline Touches
Polygon” query shown in Table 2.

Code 1. Spatial SQL Query

SELECT COUNT(*) FROM edges ed, arealm al WHERE ST_Touches(ed.geom, al.geom);


On Wed, Jan 27, 2016 at 8:56 PM, Lars Aksel Opsahl 
wrote:

> Hi
>
>
> We have done some testing on this using a single Postgis server.
>
>
> -layer 1 has 7924019 rows with 11 columns and about 1 billion points.
>
> -layer 2 has 1088614 rows with 20 columns.
>
> Both layers covers all of Norway.
>
>
> I do a “esri” union in a psql function and get a new table with 27852836
> rows and 30 columns with multipolygon. The size of the new table is about
> 40 GB.
>
>
> This is done in less than 3 hours (real 152m8.248s)
>
>
> I have made a psql function called func_esri.get_esri_union that I calls
> shown below.
>
>
> psql -t -q -o /tmp/vroom2.sql sl -c"drop table IF EXISTS sl_lop.r1; drop
> table IF EXISTS sl_lop.c1; select
> func_esri.get_esri_union('org_ar5arsversjon.ar5_2013_komm_flate id
> geo','org_ar.ar250_flate sl_sdeid geo', 'sl_lop.r1','sl_lop.c1',3000,false)"
>
>
> Then I take the output from this function and uses Gnu parallel to run the
> computed sqls in 20 parallel threads.
>
>
> time parallel -j 20 psql -h vroom2 -U postgres sl -c  /tmp/vroom2.sql
>
>
> This is fast Postgis server with ssd disks and a lot of memory and cpu.
>
>
> The basic idea is that I use
> https://github.com/larsop/content_balanced_grid/ to make a grid and then
> create sqls adjusted for this grid. The size of the cells varies a lot. The
> 3000 parameter in the sql function sets the limit to max 3000 bounding box
> pr cell.
>
>
> I will post the code on git hub as soon as I have time, I need to clean it
> up and make some comments first.
>
>
> We also did a small comparison with Arcgis where we ran on a small subset
> of the tables and we got result file with 186372 rows. That took about 5
> minutes with Arcgis software and 1 minute in postgres. This test was
> running on smaller postgres database server.
>
>
> Since there are different hardware for Arcgis and Postgis I will not put
> much in to this comparison, but my point is Postgis scales very good on big
> data having the right hardware and software.
>
>
> Lars
>
>
> 
> Fra: postgis-users [postgis-users-boun...@lists.osgeo.org] på vegne av
> Ravi Pavuluri [ravith...@ymail.com]
> Sendt: 27. januar 2016 21:31
> Til: PostGIS Users Discussion
> Emne: Re: [postgis-users] Geoprocessing & BigData
>
> Hi David,
>
> I are dealing with census blocks/census block groups spanning a few
> million records.
>
> Thanks,
> Ravi.
>
> On Monday, January 25, 2016 10:18 AM, David Haynes 
> wrote:
>
>
> We have done some work, implementing parallel spatial queries using a
> spatial declustering algorithm. How large are your datasets?
>
> On Mon, Jan 18, 2016 at 1:51 PM, Rémi Cura 

Re: [postgis-users] tiger geocoder address normalization issue (bug?)

[James Marca]

On Thu, Jan 28, 2016 at 06:15:52PM -0500, Stephen Woodbridge wrote:
> Hi James,
> 
> Thanks for reporting this. I'm hoping to have time this spring to work 
> with Regina to do a major rewrite of the geocoder. The existing code is 
> very Tiger centric and Tiger data has a lot of oddities in it some of 
> which are built into the the existing code.
> 
> The address standardizer is a new facility that was added last year and 
> I have used it to build a few geocoders that are more data agnostic. I 
> have just started working on a new address standardizer written in C++ 
> that I hope will be easier to understand and maintain and it already has 
> support for UTF8 data so it will support European language addressing.
> 
> For the curious or those that might want to help, you can checkout:
> https://github.com/woodbri/address-standardizer
> https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md

Cool.  I will check that out.  I won't help with coding though as my
C++ skills are rusty.

(and by the way I just noticed that I copy/pasted the wrong thing in
the report below...the first pair should both the us_ variants and are
in fact identical)

Regards,
James 
> 
> So what does this mean with regards to the report? Well things are 
> changing and hopefully the bad stuff is going away to be replaced by a 
> better more generic solution that will have a new set of issues, but 
> hopefully a small set of them and ones that are easier to fix.
> 
> -Steve
>  http://imaptools.com
> 
> On 1/28/2016 4:39 PM, James Marca wrote:
> >Hi,
> >
> >My goal is to use the address standarizer to match streets from two
> >different data sources.
> >
> >Test case is 8th Street in Alameda CA
> >
> >I was reading through the docs and found standardize_address
> >(http://postgis.net/docs/standardize_address.html), along with two
> >different ways to invoke it...using the us_[lex,gaz,rules] and the
> >tiger.pagc[lex,gaz,rules].
> >
> >I think I found an issue of sorts.
> >
> >First, using the 'us_' variant, all is well.  If I query with Eighth
> >Street or 8th Street, I expect the street name to be the same so I can
> >match them, and they are:
> >
> >hpms_geocode=# SELECT name,suftype FROM standardize_address('us_lex', 
> >'us_gaz', 'us_rules', '1 8th St, Alameda, CA');
> >  name | suftype
> >--+-
> >  8| STREET
> >(1 row)
> >
> >hpms_geocode=# SELECT name,suftype FROM 
> >standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' 
> >, '1 8th St, Alameda, CA');
> >  name | suftype
> >--+-
> >  8| ST
> >(1 row)
> >
> >
> >However, if I do the same with the tiger.pagc variant, the results do
> >not match:
> >
> >hpms_geocode=# SELECT name,suftype FROM 
> >standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' 
> >, '1 8th St, Alameda, CA');
> >  name | suftype
> >--+-
> >  8| ST
> >(1 row)
> >
> >hpms_geocode=# SELECT name,suftype FROM 
> >standardize_address('tiger.pagc_lex','tiger.pagc_gaz', 'tiger.pagc_rules' 
> >, '1 Eighth St, Alameda, CA');
> >  name | suftype
> >--+-
> >  8TH  | ST
> >(1 row)
> >
> >
> >Obviously I will just use the 'us_' version, but if the 'tiger.pagc_'
> >version is maintained, I think this is a bug and should be fixed
> >
> >Regards,
> >James
> >
> >
> >
> >
> >
> >___
> >postgis-users mailing list
> >postgis-users@lists.osgeo.org
> >http://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> 
> 
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users


pgp4Az7VMjqJh.pgp
Description: PGP signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users