Raoul Ross | 28 Jul 23:50 2015

Moving to a Virtual Server

Hello, my name is Raoul Ross and I’ve taken over for Eric VanHandel as the IT Manager here at Continental Mapping Consultants.

 

Through a vendor/Consultant we are moving off the very old servers here to Virtual Servers. The question came up, ‘can we just copy it all over, using the same machine name, IP address and NIC MAC Address?’ So I’m asking… will it work? If not, how do I do this? Will I need to re-install everything?

 

Don’t know where else to start… Thanx for your time!

-Raoul Ross

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Ahmet Temiz | 27 Jul 16:16 2015
Picon

How we get raster values

 Hello,

How can we get raster values of a map that intersects with certian part of a polygon map ?

 regards

--
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu


________________________

Ahmet Temiz
Geological Eng.
Information Systems - GIS Group
Disaster and Emergency Management
of Presidency
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Mark Wynter | 24 Jul 00:54 2015

Parallelisation provides powerful postgis performance perks (script + ppt slides)

A couple of tutorials on the subject... With full code on github

http://dimensionaledge.com/intro-vector-tiling-map-reduce-postgis/

http://dimensionaledge.com/from-days-to-minutes-geoprocessing-of-alberta-land-use-data/

> When I briefly look at the text you have written in the "Quick Example" It
> seems that you are distributing your query by an ID field. I am wondering
> how your method would apply to raster datasets? Distributing geographic
> data by an ID can get you into problems because of the dependency for
> certain analytical functions.

Gnu parallel great for processing complex pipelines. Mix and match with PostGis vectors and rasters,
grass, R, gDAL etc 

iD is simplest way...  But your job list can have multiple arguments which you can feed into plpgsql function
that would be called in the worker function. 

You can build in as much sophistication as you like into the plpgsql function.

Some things to bear in mind - get your querys working efficiently before scaling out - otherwise you are
scaling out bad practice.
Batch processing faster than individual processing
And Dump your multipolgons into individual polygons if do intersection analysis.

Another parsllelusation tool is R via pl/r, which I'm using for routing analysis. More specialized and not
always as versatile as Gnu parallel.

hTH.
Mark
Graeme B. Bell | 23 Jul 15:54 2015
Picon

Parallelisation provides powerful postgis performance perks (script + ppt slides) [x-posted: pgsql-performance]

Hi all,

Do you run map intersections between national scale geometry maps in postgis? (or other long-running GIS operations?)
Do you hate that feeling of waiting all day/week for the query to complete?
Well, here's a solution for you.

1. For those that don't like par_psql (http://github.com/gbb/par_psql), this alternative approach
uses the Gnu Parallel command to organise parallelism for queries that take days to run usually. We saw up
to 20x performance improvements here, a day's work in one hour. May give you a few ideas about how to
parallelise your own code with Gnu Parallel. 

https://github.com/gbb/fast_map_intersection

2. Also, I gave a talk at FOSS4G Como about these tools (and a few others), and how to get better performance
from your PostGIS database with parallelisation. 
This may be helpful to people who are new to parallelisation / multi-core work with postgres for GIS work. 

http://graemebell.net/foss4gcomo.pdf          

Enjoy,

Graeme Bell.
David Haynes | 22 Jul 22:40 2015
Picon

ST_Clip

Hello

I believe I am in appropriately using the ST_Clip function on a multiband raster. I have a raster with 13 bands and I want to retrieve from the clip bands # 1,3

How do I do that?

I tried using:
array[1,3] as bnd

Didn't work still returns 13 bands.

with bnd_num as
(
select array[1,3] as bnd
)
select p.geoid, p.label, ST_Numbands(ST_CLIP(r.rast,p.geom,b.bnd, True))
from bnd_num b, polygon p inner join modis_igbp_stack r on ST_Intersects(r.rast, p.geom);
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
juli g. pausas | 22 Jul 14:39 2015
Picon

ST_SummaryStats(ST_Clip(rast, geom)) with tiles

Hi
I have a raster from which I'd to compute some stats. The raster have with tiles, so

SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.prova
 -- give me the stats for each  tiles

SELECT (ST_SummaryStats(ST_Union(rast), 1)).* FROM rastertmp.prova
 -- give me the stats for all raster map, OK


Now I'd like to intersect with a polygon map (ecoregion) and get the stats for each region:

SELECT ecoregion_cod, (res).* FROM
  (SELECT p.ecoregion_cod, ST_SummaryStats(ST_Clip(r.rast,1, p.geom, true)) AS res
    FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r
    WHERE ST_Intersects(r.rast, p.geom)
    GROUP BY ecoregion_cod, res) AS foo; 

this give me the stats for each region in each tile, but I'd like the overall stats, i.e. the stats for each region in the whole raster. How can I do it?

Including ST_Union before clipping didn't wok ..
ERROR: aggregate functions are not allowed in GROUP BY
Any other way?


The only thing I can think off is to do it 'manually' using ST_ValueCount:

SELECT ecoregion_cod, SUM(valor * suma)/SUM(suma) AS mean, SUM(suma) AS n, MIN(valor) AS min, MAX(valor) AS max
FROM
(SELECT ecoregion_cod, (res).value AS valor, SUM((res).count) AS suma
FROM
  (SELECT p.ecoregion_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS res
    FROM gis_wd.wd_ecoregiones AS p, rastertmp.provanet AS r
    WHERE ST_Intersects(r.rast, p.geom)
   ) AS foo
GROUP BY ecoregion_cod, valor
) AS foo2
GROUP BY ecoregion_cod
ORDER BY ecoregion_cod;

In this  way I can compute the mean (and min, max), but I cannot compute StdDev from ValueCount. Is there a way?

Thanks for any suggestion
Regards

Juli
--
CIDE, CSIC  |  www.uv.es/jgpausas  |

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Moen, Paul T. | 22 Jul 14:18 2015

Re: [postgis-devel] Extension upgrade has no update path 2.1.1 to 2.1.7

Thank you for the reply.

I think it is a problem with the installer we use from EnterpriseDB and
not a bug with PostGIS.

I tried installing PostgreSQL and Postgis using a different installer from
KyngChaos.  Once everything was installed I compared files in the
directories and found many missing files.  I assume these files are
created when compiling the code?

I am using the PostgreSQL/Stackbuilder combination from EnterpriseDB and I
believe the installer does not include the appropriate files.  I see on
PostGIS¹s homepage, there is a recommendation not to use EnterpriseDB, due
to mixed reliability. Case in point. We however, do use them and will be
contacting them with the problem. I will send an update if anyone is
interested when they get this fixed.



postgis--2.0.0--2.1.7.sql
postgis--2.0.1--2.1.7.sql
postgis--2.0.2--2.1.7.sql
postgis--2.0.3--2.1.7.sql
postgis--2.0.4--2.1.7.sql
postgis--2.0.5--2.1.7.sql
postgis--2.0.6--2.1.7.sql
postgis--2.1.0--2.1.7.sql
postgis--2.1.0rc1--2.1.7.sql
postgis--2.1.0rc2--2.1.7.sql
postgis--2.1.0rc3--2.1.7.sql
postgis--2.1.1--2.1.7.sql
postgis--2.1.2--2.1.7.sql
postgis--2.1.3--2.1.7.sql
postgis--2.1.4--2.1.7.sql
postgis--2.1.5--2.1.7.sql
postgis--2.1.6--2.1.7.sql
postgis--2.1.7--2.1.7next.sql
postgis--2.1.7next--2.1.7.sql
postgis--unpackaged--2.1.7.sql
postgis_tiger_geocoder--2.0.0--2.1.7.sql
postgis_tiger_geocoder--2.0.1--2.1.7.sql
postgis_tiger_geocoder--2.0.2--2.1.7.sql
postgis_tiger_geocoder--2.0.3--2.1.7.sql
postgis_tiger_geocoder--2.0.4--2.1.7.sql
postgis_tiger_geocoder--2.0.5--2.1.7.sql
postgis_tiger_geocoder--2.0.6--2.1.7.sql
postgis_tiger_geocoder--2.1.0--2.1.7.sql
postgis_tiger_geocoder--2.1.0rc1--2.1.7.sql
postgis_tiger_geocoder--2.1.0rc2--2.1.7.sql
postgis_tiger_geocoder--2.1.0rc3--2.1.7.sql
postgis_tiger_geocoder--2.1.1--2.1.7.sql
postgis_tiger_geocoder--2.1.2--2.1.7.sql
postgis_tiger_geocoder--2.1.3--2.1.7.sql
postgis_tiger_geocoder--2.1.4--2.1.7.sql
postgis_tiger_geocoder--2.1.5--2.1.7.sql
postgis_tiger_geocoder--2.1.6--2.1.7.sql
postgis_tiger_geocoder--2.1.7--2.1.7next.sql
postgis_tiger_geocoder--2.1.7next--2.1.7.sql
postgis_tiger_geocoder--unpackaged--2.1.7.sql
postgis_topology--2.0.0--2.1.7.sql
postgis_topology--2.0.1--2.1.7.sql
postgis_topology--2.0.2--2.1.7.sql
postgis_topology--2.0.3--2.1.7.sql
postgis_topology--2.0.4--2.1.7.sql
postgis_topology--2.0.5--2.1.7.sql
postgis_topology--2.0.6--2.1.7.sql
postgis_topology--2.1.0--2.1.7.sql
postgis_topology--2.1.0rc1--2.1.7.sql
postgis_topology--2.1.0rc2--2.1.7.sql
postgis_topology--2.1.0rc3--2.1.7.sql
postgis_topology--2.1.1--2.1.7.sql
postgis_topology--2.1.2--2.1.7.sql
postgis_topology--2.1.3--2.1.7.sql
postgis_topology--2.1.4--2.1.7.sql
postgis_topology--2.1.5--2.1.7.sql
postgis_topology--2.1.6--2.1.7.sql
postgis_topology--2.1.7--2.1.7next.sql
postgis_topology--2.1.7next--2.1.7.sql
postgis_topology--unpackaged--2.1.7.sql




On 7/22/15, 1:22 AM, "Paragon Corporation" <lr <at> pcorp.us> wrote:

>Paul,
>
>I just checked my build and I have a 2.1.1-2.1.7.sql so whatever the
>issue is I don't think is a bug in PostGIS.
>
>Can you check your share/extension folder of your postgresql install and
>verify that you do have a  file called postgis-2.1.1--2.1.7.sql ?
>
>I suspect you don't.  If you do have all the postgis extension files you
>should also have a file called postgis--2.1.7.sql
>
>
>Did you build PostGIS yourself or got from a distribution?  Could be a
>packaging flaw that you need to report to the package maintainer that you
>are missing extension files.
>
>
>Thanks,
>Regina
>http://www.postgis.us

>http://postgis.net

>
>
>-----Original Message-----
>From: postgis-devel-bounces <at> lists.osgeo.org
>[mailto:postgis-devel-bounces <at> lists.osgeo.org] On Behalf Of Sandro
>Santilli
>Sent: Tuesday, July 21, 2015 12:11 PM
>To: Moen, Paul T. <pmoen <at> nd.gov>
>Cc: postgis-devel <at> lists.osgeo.org
>Subject: Re: [postgis-devel] [postgis-users] Extension upgrade has no
>update path 2.1.1 to 2.1.7
>
>On Mon, Jul 20, 2015 at 08:25:04PM +0000, Moen, Paul T. wrote:
>> I am upgrading from postgis 2.1.1 to 2.1.7 using extensions.
>> 
>> After entering the following,
>> 
>> ALTER EXTENSION postgis UPDATE TO "2.1.7�;
>> 
>> I get error
>> 
>> ERROR:  extension "postgis" has no update path from version "2.1.1" to
>>version "2.1.7"
>
>Sounds like a bug, could you please file it on trac ?
>http://trac.osgeo.org/postgis/

>
>--strk;
>_______________________________________________
>postgis-devel mailing list
>postgis-devel <at> lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

>
>

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Moen, Paul T. | 20 Jul 22:25 2015

Extension upgrade has no update path 2.1.1 to 2.1.7

I am upgrading from postgis 2.1.1 to 2.1.7 using extensions.

After entering the following,

ALTER EXTENSION postgis UPDATE TO "2.1.7”;

I get error 

ERROR:  extension "postgis" has no update path from version "2.1.1" to version "2.1.7"


********** Error **********


ERROR: extension "postgis" has no update path from version "2.1.1" to version "2.1.7"

SQL state: 22023



The documentation says

If you get an error notice something like:

No migration path defined for ... to 2.1.9dev

Then you'll need to backup your database, create a fresh one as described in Section 2.6, “Creating a spatial database using EXTENSIONS” and then restore your backup ontop of this new database.


Is it typical to have no update path within 2.1.x versions?  How many versions typically have an upgrade path?  Am I wrong in saying that extensions have a worse upgradeability within 2.1.x versions as compared to not using the extensions or would the postgis_upgrade_21_minor.sql also fail going from 2.1.1 to 2.1.7.

Thanks for any insight,

Paul

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Elmehdi OUADOUD | 20 Jul 15:14 2015
Picon

Re: St_dumpAsPolygons function is too slow

Hi,

I wonder know if the very very slow process is normal or i'm missing something?

thanks in advance

Le mercredi 15 juillet 2015 10:11:46 UTC+2, Elmehdi OUADOUD a écrit :
Hi All, 
 
I want to transfom a raster data to polygons but the process is too slow it takes 35 minutes.
The querie i'm using : 

       select (ST_DumpAsPolygons(a.raster)).geom, (ST_DumpAsPolygons(a.raster)).val
       from my_raster a

Dimensions of my_raster

X: 5261 Y: 4401


I'm missing something or it's normal ?


_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Lucas Ferreira Mation | 19 Jul 16:53 2015
Picon

Update_GeometrySRID does not alter polygons (when it should)

I'm  finding weird that Update_GeometrySRID() from SIRGAS2000 (EPSG:4674) to SAD69 (EPSG: 4618) does not alter the way polygons are rendered. 

any ideas on what is going on?

regards
Lucas Mation
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Rémi Cura | 17 Jul 18:33 2015
Picon

Re: Split(line, point), a temporary version until ST_Split gets fixed

Hey,
overall seems like you should use postgis topology
(or GRASS GIS).

Secondly,
I'm afraid you don't use the function correctly.

You should
- for each line
  - find points that may cut the line
  - group those points into a blade
  - cut the line with the blade

Which is not at all what you do .

Your querry should look like this (provided you don't use postgis topology, which has been designed exactly for your use case)

WITH points_intersecting_lines AS( --for each line, which point shall intersect it
   SELECT lines.id AS lines_id, lines.geom AS line_geom,  ST_Collect(points.geom) AS blade
   FROM lines, points
   WHERE st_dwithin(lines.geom, points.geom, your_tolerance) = true
   GROUP BY lines.id, lines.geom
)
SELECT lines_id, rc_split(line_geom, blade, your_tolerancy)
FROM points_intersecting_lines
--note : lines.geom and points.geom should have GIST index

it should take less than a minute (obviously not tested here! )

If perf is a big issue, I'd recommand using st_snap to grid, and st_node.

Cheers,
Rémi-C

2015-07-16 20:06 GMT+02:00 Vladimir Ezequiel Bellini <vlasvlasvlas <at> gmail.com>:
nicely done..

the thing is:

st_union table with lines (Crossing lines, like street lines) : 1200 rows (lines)
st_union table with points (intersection points of this lines): 1700 rows (points)

the test with rc_split_line_by_points (replace lines & points with my tables and kept tolerance 4) is really slow.. like 2 hours ago and still going......... is this normal?

i already did the st_dump(st_split way and this also is a huge delay.. and the result is bringing me lots of garbage that i must replace after.


------------------------------
## 1 First attempt (really long, without discint / clean table is a whole mess like millions of rows). And this takes really forever..really slow..like 6hrs : 1200lines, 1700 points

--first
CREATE TABLE lines_with_mess AS (
SELECT
((ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom) as geom
FROM st_union_lines a
INNER JOIN lots_of_points b
ON ST_INTERSECTS(a.geom, b.ix)
);
--then
create table lines_clean_segments as (
SELECT DISTINCT ON (ST_AsBinary(geom)) 
geom 
FROM 
lines_with_mess
);


--------------

---------------------------------
## 2nd attempt: cleaner but still takes really a lot of time......

--first, really gets also messy, lots LOTS of unwanted rows in result
Create table lines_segments as 
  Select 
    row_number() over() as id,
    (st_dump(st_split(input.geom, blade.ix))).geom as geom
  from st_union_lines input
  inner join lots_of_points blade on st_intersects(input.geom, blade.ix);
  
--then cleaning the table
delete from lines_segments a
where exists 
  (
  select 1 from lines_segments b where a.id != b.id and st_coveredby(b.geom,a.geom)
  );

  
--------------------- 


Now i'm testing your function but i still got really delayed for result and dunno if i got messy rows..


...

:\

Vladimir.


El martes, 4 de marzo de 2014, 12:54:33 (UTC-3), Rémi Cura escribió:
Hey List,
here is a link a link to a working implementation of ST_Split((multi)line,(multi)point).
The current ST_Split is not working correctly with points due to precision issues (and it is not working with multi ofc).

The proposed function hacks the Curvilinear api (http://postgis.refractions.net/docs/ST_Line_Locate_Point.html, etc), in order to escape the precision issue that plagues all point related stuff in postgis.

Tolerance is fully supported (no segment too small can be generated with a filtering on split point, only split if close enough).

I didn't benchmarked it, but I would expect it to be way slower than the C (non-working) function. This is totally non-optimal but is  temporary until the necessary changes are made on ST_Split.

Function is tested for 2.0

Cheers,
Rémi-C

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane