Re: [postgis-users] toTopoGeom performance tips

[Luca Morandini]

On 05/16/2012 06:19 PM, Sandro Santilli wrote:

On Tue, May 15, 2012 at 03:45:13PM +1000, Luca Morandini wrote:

It took nearly 4 times as much - I divided the load in chunks of 100
polygons each - hence it seems one big transaction is better than
many small ones.


Ok, then it's not an issue with transactions.

If you want to help further profiling please:
  (1) install latest GEOS from 3.3 branch
  (2) install latest postgis from trunk (make sure to upgrade topology scripts)
  (3) see how time relates to topology primitives population density, see if a
  specific geometry is taking a visible lot more than others to import,
  enable debugging in topology to figure where the time goes.


Once more on the bleeding edge... I have talked to the project manager, and we are 
willing to help; just allow me three or four working days to set up a test bed 
according to your requirements.




PS: your queries do look fine.


Thanks for checking.

Regards,

Luca Morandini
Data Architect - AURIN project
Department of Computing and Information Systems
University of Melbourne

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] use index for "order by xmin(geom)"

[Mike Toews]

On 17 May 2012 09:10, Melchior Moos  wrote:
> Sorry, actually st_xmin is the function I'm after. I want to select
> the whole table ordered by minimum x coordinate of the gemertries.
> When I do it the naive way postgresql needs half an hour to prepare
> the ordering before the first results are delivered, since my table is
> quite large. I thought that it could be somehow possible to skip this
> time since the ordering is already stored in the index on the geometry
> column...

One way to do this is to make an index using that function:

CREATE INDEX mytable_xmin_idx ON mytable (ST_Xmin(geom));

Then check the planner to make sure the index is being used:

EXPLAIN ANALYSE SELECT *
FROM mytable
ORDER BY ST_Xmin(geom);

See if that speeds things up.
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] use index for "order by xmin(geom)"

[Melchior Moos]

> "xmin" is actually a PostgreSQL system column used for transactions,
> not a PostGIS function.

Sorry, actually st_xmin is the function I'm after. I want to select
the whole table ordered by minimum x coordinate of the gemertries.
When I do it the naive way postgresql needs half an hour to prepare
the ordering before the first results are delivered, since my table is
quite large. I thought that it could be somehow possible to skip this
time since the ordering is already stored in the index on the geometry
column...

On 17 May 2012 03:05, Melchior Moos  wrote:
> I recently read that postgis 2.0 can use the index in order by clauses
> to find nearest neighbours of geometries. Is there also a way to use
> the index for queries like
> SELECT * FROM xy ORDER BY xmin(geom); ?
> Best regards,
> Melchior Moos
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Linear referencing with PostGIS

[Melpati, Muni]

We have various web-based GIS applications that consume the data from 
enterprise database (oracle/ArcSDE) . These applications need linear 
referencing capabilities of the ArcSDE. We currently do this using java API 
provided by ESRI. Some of these web application provide user capabilities to 
query distance, in miles, of a road segment give a location on the road. Our 
data has linear referencing capabilities and other features such as topology 
which are essential to our business needs. I came to know while older PostGIS 
doesn't provide such capabilities. I am wondering if newer versions offer any 
similar functionalities. Is it possible to provide similar capabilities with 
PostGIS and Postgresql? Thanks in advance.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] use index for "order by xmin(geom)"

[Mike Toews]

Hi Melchior,

"xmin" is actually a PostgreSQL system column used for transactions,
not a PostGIS function.

http://www.postgresql.org/docs/current/static/ddl-system-columns.html

The KNN nearest neighbour feature that I think you are describing is
the <-> and <#> operators. Check out the documentation for more:

http://postgis.refractions.net/docs/geometry_distance_centroid.html
http://postgis.refractions.net/docs/geometry_distance_box.html

-Mike

On 17 May 2012 03:05, Melchior Moos  wrote:
> I recently read that postgis 2.0 can use the index in order by clauses
> to find nearest neighbours of geometries. Is there also a way to use
> the index for queries like
> SELECT * FROM xy ORDER BY xmin(geom); ?
> Best regards,
> Melchior Moos
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Closing polylines

[pcreso]

Hi George,

There is a simple example of a script using topology on the wiki at:
http://trac.osgeo.org/postgis/wiki/UsersWikiTopologyExample
& more help at
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgisTopology


The attached script was written to take the New Zealand mainland coastline 
comprised of various linestrings which had small gaps between them in some 
cases, & build a polygon from them. To do this it iterated through the 
linestrings, loading them into the topology one at a time with a small snapping 
distance to ensure all the start/end nodes aligned correctly. 

It also loads data from various shapefiles of island polygons into the table & 
exports a shapefile of the entire national coastline as polygons. I think you 
can pull out the appropriate SQL commands from this to do what you are asking 
about.

I haven't tried this with multilinestrings, only single linestrings. 

If you need additional help, feel free to ask, though I'm just learning the 
topology stuff myself.

Cheers,

   Brent Wood


--- On Wed, 5/16/12, george wash  wrote:

From: george wash 
Subject: Re: [postgis-users] Closing polylines
To: pcr...@pcreso.com
Date: Wednesday, May 16, 2012, 12:28 PM


  


  
  
On 5/16/2012 4:16 AM, pcr...@pcreso.com wrote:

  

  
You can work around
  this problem by using the Postgis topology capability. It
  will build polygons (faces) from such lines which you can
  use as topopolygons or extract as conventional geometries.

  

  --- On Tue, 5/15/12, george wash 
  wrote:


  

  

thank you pcreso, I had a look at the topology
  section of the PostGIS manual, but I must admit I am going
to need some guidance as to how to proceed as I never used it
before.

Given the attachment in my earlier post, which is a dump of a simple
geometry table in the public schema, how do you suggest I proceed
with creating the topology and how do I then extract the closed
contours from it? Many thanks



  



mk_coast
Description: Binary data
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] (no subject)

[Kurt]

SingIe m0m makes reaI m0ney w0rking at h0me 0nIine
http://mavin21c.dothome.co.kr/httpwagregerw2.php?ynisyahooID=724





"  You cant prove anythingon him, you know; everything will be quieted down 
then, and hell walk inHucks money as easy as nothing." (c) annye aeker
Wed, 16 May 2012 18:07:04
  ___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] use index for "order by xmin(geom)"

[Melchior Moos]

I recently read that postgis 2.0 can use the index in order by clauses
to find nearest neighbours of geometries. Is there also a way to use
the index for queries like
SELECT * FROM xy ORDER BY xmin(geom); ?
Best regards,
Melchior Moos
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Closing polylines

[Nicolas Ribot]

Hi,

I will have a look at it.

Nicolas

On 15 May 2012 11:17, george wash  wrote:
> Sorry Nicolas, the attachment is in a normal dump format, not shapefile
> format
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS KNN best practices

[Stephen V. Mather]

Ah, I had hopes pinned on the idea that I just wasn't smart enough to figure
it out, but it's an inherent limitation.  I will be using your function
though-that is a clean way to encapsulate the functionality.

 

 

http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243

s...@clevelandmetroparks.com
  clevelandmetroparks.com

 

 

 

 

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Alexandre Neto
Sent: Wednesday, May 16, 2012 7:35 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PostGIS KNN best practices

 

I have been around that question to.

 

http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-pos
tgis-2-0-using-gist-index-function 

 

You have to do it in two steps, like is explained in the operator page
 . One
faster step to reduce the candidates (by using <-> or <#>) and second one to
get the real distances with ST_Distance.

 

The problem in finding the KNN for each row in a table is the fact that the
gist index <-> operator only works if one of the geometries is constant. The
workaround would be to create a SQL function to apply to each of the rows
using table.the_geom as a parameter.

 

Something like this:

 



CREATE OR REPLACE FUNCTION _enn2 (geometry) RETURNS double precision AS $$

 

WITH index_query as

(SELECT ST_Distance($1,f.the_geom) as dist

FROM "grelha5m" As f

ORDER BY $1 <#> g1.the_geom limit 1000)

SELECT dist

FROM index_query

ORDER BY dist;

 

$$ LANGUAGE SQL;

---

 

and I call it like this: 

 

---

Select c.gid as gid, _enn2(c.the_geom) as enn

>From cosn1 as c

Order by c.gid

---

 

In this case the function returned the smallest distance, but you can choose
any other column.

 

Hope it helps

 

Alexandre Neto

 

On Tue, May 15, 2012 at 5:37 PM, Stephen V. Mather
 wrote:

Hi All,

Pretty excited by the new operators <-> and <#>, but a bit
confused as to how to use them in a query.  The two examples from P. Ramsey
back in late 2011 (
http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postg
is/ ) included doing a KNN on a single point to a cloud of points, i.e. 

 

SELECT name, gid

FROM geonames

ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)

LIMIT 10;

 

or doing KNN on non-point different geometries, where the first neighbor by
<-> or <#> might not be truly the first i.e.

 

with index_query as (
  select
st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance,
parcel_id, address
  from parcels
  order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100
)
select * from index_query order by distance limit 10;

 

So, how would one grab the first nearest neighbor for all points in a
dataset?  This is how I used to do it:

 

CREATE TABLE n2180_560_height AS 

SELECT x, y, height FROM 

(SELECT DISTINCT ON(veg.gid) veg.gid as gid, ground.gid as gid_ground, veg.x
as x, veg.y as y, ground.z as z, veg.z - ground.z as height, veg.the_geom as
geometry, veg.class as class

FROM (SELECT * FROM n2180_560 WHERE class = 5) As veg, (SELECT * FROM
n2180_560 WHERE class = 2) As ground

WHERE veg.class = 5 AND veg.gid <> ground.gid AND ST_DWithin(veg.the_geom,
ground.the_geom, 10)

ORDER BY veg.gid, ST_Distance(veg.the_geom,ground.the_geom)) AS vegpoints;

 

ST_DWithin prevents a full cross join, but is a sloppy way to do this, as it
requires a priori knowledge of the end cases.  I'm hoping there's a subquery
or some such magic that would allow me to use the distance operator to a
similar end. .

 

Thanks,

Best,

Steve

 

http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216)   635-3243

s...@clevelandmetroparks.com
  clevelandmetroparks.com

 

 

 

 


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

 

<>___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] segmentation fault

[Gold, Jack L (US SSA)]

It seems that going through each of the individual libraries (proj.4, geos, 
gdal, and postgis-2.0) and running make clean, make install yielded new 
libraries that worked without the segfault.  I'm thinking that old 32-bit 
object files were lying around in the previous build environment and something 
managed to link incorrectly.

--Jack Gold

-Original Message-
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Gold, Jack 
L (US SSA)
Sent: Tuesday, May 15, 2012 8:43 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] segmentation fault

I decided to run a core dump and see if I can spot why the call to the library 
is causing the crash.  I still don't know what is causing the crash, but I 
suspect it's one of the features I compiled into the postgis library.  Below is 
the dump and a file listing that indicates that all the libraries are indeed 
x86_64:

[root@localhost data]# gdb /usr/pgsql-9.1/bin/postgres core.3252 GNU gdb (GDB) 
CentOS (7.0.1-42.el5.centos) Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /usr/pgsql-9.1/bin/postgres...(no debugging symbols 
found)...done.

warning: core file may not match specified executable file.
[New Thread 3252]
Reading symbols from /usr/lib64/libxslt.so.1...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib64/libxslt.so.1 Reading symbols from 
/usr/lib64/libxml2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxml2.so.2 Reading symbols from 
/lib64/libpam.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib64/libpam.so.0
Reading symbols from /lib64/libssl.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libssl.so.6
Reading symbols from /lib64/libcrypto.so.6...(no debugging symbols 
found)...done.
Loaded symbols for /lib64/libcrypto.so.6 Reading symbols from 
/usr/lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libgssapi_krb5.so.2 Reading symbols from 
/lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /usr/lib64/libldap-2.3.so.0...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib64/libldap-2.3.so.0 Reading symbols from 
/lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /usr/lib64/libkrb5.so.3...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib64/libkrb5.so.3 Reading symbols from 
/lib64/libcom_err.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcom_err.so.2 Reading symbols from 
/lib64/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libz.so.1
Reading symbols from /lib64/libaudit.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib64/libaudit.so.0
Reading symbols from /usr/lib64/libk5crypto.so.3...(no debugging symbols 
found)...done.
Loaded symbols for /usr/lib64/libk5crypto.so.3 Reading symbols from 
/usr/lib64/libkrb5support.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libkrb5support.so.0 Reading symbols from 
/lib64/libkeyutils.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libkeyutils.so.1 Reading symbols from 
/lib64/libresolv.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libresolv.so.2 Reading symbols from 
/lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from 
/usr/lib64/liblber-2.3.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/liblber-2.3.so.0 Reading symbols from 
/usr/lib64/libsasl2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libsasl2.so.2 Reading symbols from 
/lib64/libselinux.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libselinux.so.1 Reading symbols from 
/lib64/libsepol.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libsepol.so.1
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols 
found)...done.
Loaded symbols for /lib64/libnss_files.so.2 Reading symbols from 
/usr/pgsql-9.1/lib/postgis-2.0.so...done.
Loaded symbols for /usr/pgsql-9.1/lib/postgis-2.0.so Reading symbols from 
/usr/local/lib/libgeos_c.s

Re: [postgis-users] PostGIS KNN best practices

[Alexandre Neto]

I have been around that question to.

http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-postgis-2-0-using-gist-index-function


You have to do it in two steps, like is explained in the operator
page.
One faster step to reduce the candidates (by using <-> or <#>) and second
one to get the real distances with ST_Distance.

The problem in finding the KNN for each row in a table is the fact that the
gist index <-> operator only works if one of the geometries is constant.
The workaround would be to create a SQL function to apply to each of the
rows using table.the_geom as a parameter.

Something like this:


CREATE OR REPLACE FUNCTION _enn2 (geometry) RETURNS double precision AS $$

WITH index_query as
(SELECT ST_Distance($1,f.the_geom) as dist
FROM "grelha5m" As f
ORDER BY $1 <#> g1.the_geom limit 1000)
SELECT dist
FROM index_query
ORDER BY dist;

$$ LANGUAGE SQL;
---

and I call it like this:

---
Select c.gid as gid, _enn2(c.the_geom) as enn
>From cosn1 as c
Order by c.gid
---

In this case the function returned the smallest distance, but you can
choose any other column.

Hope it helps

Alexandre Neto

On Tue, May 15, 2012 at 5:37 PM, Stephen V. Mather <
s...@clevelandmetroparks.com> wrote:

>  Hi All,
>
> Pretty excited by the new operators <-> and <#>, but a bit
> confused as to how to use them in a query.  The two examples from P. Ramsey
> back in late 2011 (
> http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/)
>  included doing a KNN on a single point to a cloud of points, i.e.
> 
>
> ** **
>
> SELECT name, gid
>
> FROM geonames
>
> ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)
>
> LIMIT 10;
>
> ** **
>
> or doing KNN on non-point different geometries, where the first neighbor
> by <-> or <#> might not be truly the first i.e.
>
> ** **
>
> with index_query as (
>
>   select
>
> st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance,
>
> parcel_id, address
>
>   from parcels
>
>   order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100
>
> )
>
> select * from index_query order by distance limit 10;
>
> ** **
>
> So, how would one grab the first nearest neighbor for all points in a
> dataset?  This is how I used to do it:
>
> ** **
>
> CREATE TABLE n2180_560_height AS 
>
> SELECT x, y, height FROM 
>
> (SELECT DISTINCT ON(veg.gid) veg.gid as gid, ground.gid as gid_ground,
> veg.x as x, veg.y as y, ground.z as z, veg.z - ground.z as height,
> veg.the_geom as geometry, veg.class as class
>
> FROM (SELECT * FROM n2180_560 WHERE class = 5) As veg, (SELECT * FROM
> n2180_560 WHERE class = 2) As ground
>
> WHERE veg.class = 5 AND veg.gid <> ground.gid AND ST_DWithin(veg.the_geom,
> ground.the_geom, 10)
>
> ORDER BY veg.gid, ST_Distance(veg.the_geom,ground.the_geom)) AS vegpoints;
> 
>
> ** **
>
> ST_DWithin prevents a full cross join, but is a sloppy way to do this, as
> it requires a priori knowledge of the end cases.  I’m hoping there’s a
> subquery or some such magic that would allow me to use the distance
> operator to a similar end… .
>
> ** **
>
> Thanks,
>
> Best,
>
> Steve
>
> ** **
>
> **[image: http://www.clemetparks.com/images/esig/cmp-ms-90x122.png]**Stephen
> Mather
> Geographic Information Systems (GIS) Manager
> (216) 635-3243
>
> s...@clevelandmetroparks.com
> clevelandmetroparks.com 
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
<>___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Correct or wrong raster image loading

[Giannis Giakoumidakis]

As I found it out myself, I'll answer in case someone wants to work with that 
in the future!

The Postgis connection plugin in QGIS cannot add raster data, only shapefiles. 
In order to load, view and edit rasters in QGIS you have to add wtkraster 
plugin: http://plugins.qgis.org/plugins/wktraster/version/0.5.3/ and then works 
fine and easy.






 From: Giannis Giakoumidakis 
To: PostGIS Users Discussion  
Sent: Tuesday, May 15, 2012 11:05 AM
Subject: Re: [postgis-users] Correct or wrong raster image loading
 

All right, I got that, thank you. 


So is there anyone who uses QGIS plugin to Postgis and managed to display 
raster data, because I can't and I don't know what's the problem? Which part of 
the database I have to Add in order to display my rasters in QGIS platform?







 From: Bborie Park 
To: postgis-users@postgis.refractions.net 
Sent: Monday, May 14, 2012 10:45 PM
Subject: Re: [postgis-users] Correct or wrong raster image loading
 
Giannis,

If your tile size is the same as the raster size, you're only going to
get one tile (and one row).  Now if you were specify a tile size smaller
than the raster size (such as 30x31), you would have 117 tiles (and rows).

I wouldn't be concerned about not seeing the whole blob in pgAdmin.
pgAdmin usually does that for data that is particularly large.

-bborie

On 05/14/2012 12:07 PM, Giannis Giakoumidakis wrote:
> Thanks for the anwser.
> 
> My raster is 390x279, so I use -t 390x279. I get no errors. I didn't 
> understand from your asnwer, the rows in the table should have been more than 
> 1 that it is now or it is correct like that?
> 
> The .sql I get before I use psql is this, in Notepad++:
> 
> BEGIN;
> CREATE TABLE "public"."cc00h00m15s" ("rid" serial PRIMARY KEY,"rast" raster);
> INSERT INTO "public"."cc00h00m15s" ("rast") VALUES
 
('010400DDC80B44AA83173FF2C80B44AA8317BF1986D9154421394032D1D677B4AE4140E610860117010400828282828282828282828282...')::raster;
> ANALYZE "public"."cc00h00m15s";
> SELECT 
> AddRasterConstraints('public','cc00h00m15s','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);
> END;
> 
> I don't know why in the sql editor of pgAdmin, the: VALUES 
> ('010400DDC80B44AA83173FF2C80B44AA8317BF1986D9154421394032D1D677B4AE41400')
>  are missing.
> 
> 
> 
> 
> 
> 
> 
> 
>  From: Bborie Park 
> To: postgis-users@postgis.refractions.net 
> Sent: Monday, May 14, 2012
 8:07 PM
> Subject: Re: [postgis-users] Correct or wrong raster image loading
>  
> What are the dimensions (width/height) of your raster?  I just tested
> your raster2pgsql command string...
> 
> raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 testtable
> 
> And it has no issues.  The generated table will only have 2 or 3 columns
> (3 since you specified -F).  Each raster column will have one entry in
> the raster_columns view.
> 
> You may wan to check the SQL generated from raster2pgsql instead of
> piping directly to psql.  I'm guessing something went wrong in the
> raster loading process.  Without additional information (error messages,
> if any), we really can't say what is wrong.
> 
> -bborie
> 
> 
> On 05/14/2012 09:01 AM, Giannis Giakoumidakis wrote:
>> I'm not sure if my loading of
 raster images is correct. 
>>
>> I use "raster2pgsql -s 4236 -I -C -M *.tif -F -t 100x100 public.demelevation 
>> | psql -d gisdb" from the manual. What I get is a table that has only 3 
>> columns (rid, rast, filename) and 1 row only (I thought -t would create many 
>> rows). Also, 12 constraints. Finally, 1 row in the "raster_columns" view, 
>> which contains all the details of the loaded raster. 
>>
>>
>> The difference with a loaded shapefile is that this table has 55.000 rows 
>> that contains all the vector data details.
>>
>>
>> So I have a major problem when I connect to the database through QGIS. There 
>> I can load and display normally a shapefile from the database (it appeas 
>> with "MULTIPOLYGON" type), but all the tables which contains rasters appear 
>> with "no geometry" type and can't be displayed in QGIS. 
>>
>>
>> Can anyone tell me where is my fault, in the QGIS or
 in the first loading of the raster images in the database? Thanks.
>>
>>
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Bborie Park
Programmer
Center for Vectorborne
 Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



__

Re: [postgis-users] toTopoGeom performance tips

[Sandro Santilli]

On Tue, May 15, 2012 at 03:45:13PM +1000, Luca Morandini wrote:
> On 05/14/2012 05:38 PM, Sandro Santilli wrote:
> >On Mon, May 14, 2012 at 05:25:47PM +1000, Luca Morandini wrote:
> >>On 05/14/2012 04:39 PM, Sandro Santilli wrote:
> >
> >>>I've noticed that a single transaction takes a lot more time than multiple.
> >>>This is because toTopoGeometry is an hard database writer and the 
> >>>transaction
> >>>must keep track of all changes to eventually roll-back. The presence of
> >>>sub-transactions make things somewhat worst.
> >>
> >>I see... well, having transactions similar to those in Oracle
> >>certainly would not have hurt here.
> >
> >Please run the benchmark before jumping to conclusions.
> 
> As I see it, it is more a matter of flexibility than performance.
> 
> >Looking forward for your numbers.
> 
> It took nearly 4 times as much - I divided the load in chunks of 100
> polygons each - hence it seems one big transaction is better than
> many small ones.

Ok, then it's not an issue with transactions.

If you want to help further profiling please:
 (1) install latest GEOS from 3.3 branch
 (2) install latest postgis from trunk (make sure to upgrade topology scripts)
 (3) see how time relates to topology primitives population density, see if a
 specific geometry is taking a visible lot more than others to import,
 enable debugging in topology to figure where the time goes.

PS: your queries do look fine.

--strk;

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


> 
> Just in case I did something silly, here are the commands I sent for you to 
> check:
>INSERT INTO lga11aaustlines (ogc_fid, topology)
>  SELECT ogc_fid, topology.toTopoGeom(wkb_geometry, 'lgatopo', 1, 0)
> FROM lga11aaust
> OFFSET 0 LIMIT 100;
> ...
>INSERT INTO lga11aaustlines (ogc_fid, topology)
>  SELECT ogc_fid, topology.toTopoGeom(wkb_geometry, 'lgatopo', 1, 0)
> FROM lga11aaust
> OFFSET 500 LIMIT 100;
> 
> Regards and thanks for your time,
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users