Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?

[Robert Buckley]

Ok thanks,

Is it dangerous to delete toast tables?





Von: Greg Williamson 
An: Robert Buckley ; 
"postgis-users@postgis.refractions.net" 
; PostGIS Users Discussion 

Gesendet: 22:43 Donnerstag, 22.September 2011 
Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?

Rob --

<...>
>
>Thanks,
>
>my version is 8.4. The table contains strings but are not too long...under 
>100char.
>
>Thanks,
>


According to :

"All PostGIS objects are "varlena", they don't have a fixed size. ..."

So maybe you got some large geometries that triggered TOAST processing ?

Greg W.

>
>
>Von: Greg Williamson 
>An: Robert Buckley ; 
>"postgis-users@postgis.refractions.net" 
>; PostGIS Users Discussion 
>
>Gesendet: 21:19 Donnerstag, 22.September 2011 
>Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
>
>Rob -- 
>
>>Hi,
>>
>>
>>when I left work today these pg_toast tables were not in my database. when I 
>>looked later ther were.
>>
>>
>>Can anyone tell me where they came from and why they are automatically 
>>created in every database?
>>
>
>
>You don't state what version of postgres this happens on, but in general TOAST 
>tables are created by the system to hold long compressed values (typically 
>text aka varlena tables). I think you can turn this facility off, but in 
>general postgres will try to take very long strings, for example, and compress 
>them, putting them into a toast table to that the row size of the original 
>table doesn't grow excessively. See, for example, 
> 
>
>So I suspect what happened is that someone entered some long text values and 
>postgres created the toast tables to handle these long strings.
>
>HTH,
>
>Greg Williamson
>
>
>
>___
>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] problem with data crossing the date line

[pcreso]

Or use 0-360 longitudes for a single geometry polygon. Valid EPSG:4326 
coordinate space.

The problem then arises if you have other data in a +-180 space...

Brent Wood

--- On Fri, 9/23/11, Paul Ramsey  wrote:

From: Paul Ramsey 
Subject: Re: [postgis-users] problem with data crossing the date line
To: "PostGIS Users Discussion" 
Date: Friday, September 23, 2011, 7:47 AM

Note that while a multi-poly split at the dateline will (a) render
find and (b) give right answers to all functions, it will do bad
things (if you have enough of them) to your index, since the index
bbox of the feature will cover the whole world. So even tiny queries
in the middle of the world will end up wading through the split
features as possible cases turned up by the index.

P.

On Thu, Sep 22, 2011 at 12:40 PM, Jesse Bishop  wrote:
>
> Hi Puneet,
> We ran into this issue as well.  As a workaround for display in QGIS, we 
> store those features that cross the date line as MULTIPOLYGONS that are split 
> at 180/-180 line.  It is not ideal, but it works for display.  It does not 
> work for some operations like ST_Centroid but others like ST_Contains will 
> work.
> Jesse
> Examples:
> As displayed in QGIS:
>  . . .
> From the database perspective:
> smddb_dev=# SELECT AsText(scene_geom) FROM scene_locator WHERE id = 28716;
>                                                                               
>                     astext
> ---
>  MULTIPOLYGON(((179.665 -17.733,180 -17.6550440252,180 -18.2032131661,179.794 
> -18.251,179.665 -17.733)),((-180 -17.6550440252,-179.699 -17.585,-179.568 
> -18.103,-180 -18.2032131661,-180 -17.6550440252)))
> (1 row)
> smddb_dev=# SELECT AsText(ST_Centroid(scene_geom)) FROM scene_locator WHERE 
> id = 28716;
>                    astext
> 
>  POINT(-27.1147737791952 -17.9181176096631)
> (1 row)
>
> smddb_dev=# SELECT ST_Contains(scene_geom, GeomFromText('POINT(-179.8 
> -17.8)', 4326)) FROM scene_locator WHERE id = 28716;
>  st_contains
> -
>  t
> (1 row)
> On Sep 15, 2011, at 4:06 PM, Paul Ramsey wrote:
>
> Might want to cross-post this to QGIS, it's more their problem than ours.
>
> P
>
> On Thu, Sep 15, 2011 at 1:22 PM, Puneet Kishor  wrote:
>
> well, I've reached that point where I have to deal with data crossing the 
> date line. I have kept a file on my Dropbox/Public folder 
> [http://dl.dropbox.com/u/3526821/PA.gmt] which has a series of lat/lng pairs, 
> one per line, making a single polygon. If I insert them into PostGIS as 
> GEOMETRY, the resulting polygon is messed up. If I insert them as GEOGRAPHY, 
> I can't view the polygon in Quantum GIS.
>
> Any suggestion on the best way to handle this?
>
> Puneet.
>
> ___
>
> 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
>
>
>
> ___
> 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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?

[Greg Williamson]

Rob --

<...>
>
>Thanks,
>
>my version is 8.4. The table contains strings but are not too long...under 
>100char.
>
>Thanks,
>


According to :

"All PostGIS objects are "varlena", they don't have a fixed size. ..."

So maybe you got some large geometries that triggered TOAST processing ?

Greg W.

>
>
>Von: Greg Williamson 
>An: Robert Buckley ; 
>"postgis-users@postgis.refractions.net" 
>; PostGIS Users Discussion 
>
>Gesendet: 21:19 Donnerstag, 22.September 2011 
>Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
>
>Rob -- 
>
>>Hi,
>>
>>
>>when I left work today these pg_toast tables were not in my database. when I 
>>looked later ther were.
>>
>>
>>Can anyone tell me where they came from and why they are automatically 
>>created in every database?
>>
>
>
>You don't state what version of postgres this happens on, but in general TOAST 
>tables are created by the system to hold long compressed values (typically 
>text aka varlena tables). I think you can turn this facility off, but in 
>general postgres will try to take very long strings, for example, and compress 
>them, putting them into a toast table to that the row size of the original 
>table doesn't grow excessively. See, for example, 
> 
>
>So I suspect what happened is that someone entered some long text values and 
>postgres created the toast tables to handle these long strings.
>
>HTH,
>
>Greg Williamson
>
>
>
>___
>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] where did pg_toast,pg_toast_temp1 come from?

[Robert Buckley]

Thanks,

my version is 8.4. The table contains strings but are not too long...under 
100char.

Thanks,

Rob



Von: Greg Williamson 
An: Robert Buckley ; 
"postgis-users@postgis.refractions.net" 
; PostGIS Users Discussion 

Gesendet: 21:19 Donnerstag, 22.September 2011 
Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?

Rob -- 

>Hi,
>
>
>when I left work today these pg_toast tables were not in my database. when I 
>looked later ther were.
>
>
>Can anyone tell me where they came from and why they are automatically created 
>in every database?
>


You don't state what version of postgres this happens on, but in general TOAST 
tables are created by the system to hold long compressed values (typically text 
aka varlena tables). I think you can turn this facility off, but in general 
postgres will try to take very long strings, for example, and compress them, 
putting them into a toast table to that the row size of the original table 
doesn't grow excessively. See, for example, 
 

So I suspect what happened is that someone entered some long text values and 
postgres created the toast tables to handle these long strings.

HTH,

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

Re: [postgis-users] problem with data crossing the date line

[Paul Ramsey]

Note that while a multi-poly split at the dateline will (a) render
find and (b) give right answers to all functions, it will do bad
things (if you have enough of them) to your index, since the index
bbox of the feature will cover the whole world. So even tiny queries
in the middle of the world will end up wading through the split
features as possible cases turned up by the index.

P.

On Thu, Sep 22, 2011 at 12:40 PM, Jesse Bishop  wrote:
>
> Hi Puneet,
> We ran into this issue as well.  As a workaround for display in QGIS, we 
> store those features that cross the date line as MULTIPOLYGONS that are split 
> at 180/-180 line.  It is not ideal, but it works for display.  It does not 
> work for some operations like ST_Centroid but others like ST_Contains will 
> work.
> Jesse
> Examples:
> As displayed in QGIS:
>  . . .
> From the database perspective:
> smddb_dev=# SELECT AsText(scene_geom) FROM scene_locator WHERE id = 28716;
>                                                                               
>                     astext
> ---
>  MULTIPOLYGON(((179.665 -17.733,180 -17.6550440252,180 -18.2032131661,179.794 
> -18.251,179.665 -17.733)),((-180 -17.6550440252,-179.699 -17.585,-179.568 
> -18.103,-180 -18.2032131661,-180 -17.6550440252)))
> (1 row)
> smddb_dev=# SELECT AsText(ST_Centroid(scene_geom)) FROM scene_locator WHERE 
> id = 28716;
>                    astext
> 
>  POINT(-27.1147737791952 -17.9181176096631)
> (1 row)
>
> smddb_dev=# SELECT ST_Contains(scene_geom, GeomFromText('POINT(-179.8 
> -17.8)', 4326)) FROM scene_locator WHERE id = 28716;
>  st_contains
> -
>  t
> (1 row)
> On Sep 15, 2011, at 4:06 PM, Paul Ramsey wrote:
>
> Might want to cross-post this to QGIS, it's more their problem than ours.
>
> P
>
> On Thu, Sep 15, 2011 at 1:22 PM, Puneet Kishor  wrote:
>
> well, I've reached that point where I have to deal with data crossing the 
> date line. I have kept a file on my Dropbox/Public folder 
> [http://dl.dropbox.com/u/3526821/PA.gmt] which has a series of lat/lng pairs, 
> one per line, making a single polygon. If I insert them into PostGIS as 
> GEOMETRY, the resulting polygon is messed up. If I insert them as GEOGRAPHY, 
> I can't view the polygon in Quantum GIS.
>
> Any suggestion on the best way to handle this?
>
> Puneet.
>
> ___
>
> 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
>
>
>
> ___
> 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] problem with data crossing the date line

[Jesse Bishop]

Hi Puneet,We ran into this issue as well.  As a workaround for display in QGIS, we store those features that cross the date line as MULTIPOLYGONS that are split at 180/-180 line.  It is not ideal, but it works for display.  It does not work for some operations like ST_Centroid but others like ST_Contains will work.JesseExamples:As displayed in QGIS: . . . From the database perspective:smddb_dev=# SELECT AsText(scene_geom) FROM scene_locator WHERE id = 28716;                                                                                                  astext                                                                                                   --- MULTIPOLYGON(((179.665 -17.733,180 -17.6550440252,180 -18.2032131661,179.794 -18.251,179.665 -17.733)),((-180 -17.6550440252,-179.699 -17.585,-179.568 -18.103,-180 -18.2032131661,-180 -17.6550440252)))(1 row)smddb_dev=# SELECT AsText(ST_Centroid(scene_geom)) FROM scene_locator WHERE id = 28716;                   astext                    POINT(-27.1147737791952 -17.9181176096631)(1 row)smddb_dev=# SELECT ST_Contains(scene_geom, GeomFromText('POINT(-179.8 -17.8)', 4326)) FROM scene_locator WHERE id = 28716; st_contains - t(1 row)On Sep 15, 2011, at 4:06 PM, Paul Ramsey wrote:Might want to cross-post this to QGIS, it's more their problem than ours.POn Thu, Sep 15, 2011 at 1:22 PM, Puneet Kishor  wrote:well, I've reached that point where I have to deal with data crossing the date line. I have kept a file on my Dropbox/Public folder [http://dl.dropbox.com/u/3526821/PA.gmt] which has a series of lat/lng pairs, one per line, making a single polygon. If I insert them into PostGIS as GEOMETRY, the resulting polygon is messed up. If I insert them as GEOGRAPHY, I can't view the polygon in Quantum GIS.Any suggestion on the best way to handle this?Puneet.___postgis-users mailing listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users___postgis-users mailing listpostgis-users@postgis.refractions.nethttp://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] where did pg_toast,pg_toast_temp1 come from?

[Greg Williamson]

Rob -- 

>Hi,
>
>
>when I left work today these pg_toast tables were not in my database. when I 
>looked later ther were.
>
>
>Can anyone tell me where they came from and why they are automatically created 
>in every database?
>


You don't state what version of postgres this happens on, but in general TOAST 
tables are created by the system to hold long compressed values (typically text 
aka varlena tables). I think you can turn this facility off, but in general 
postgres will try to take very long strings, for example, and compress them, 
putting them into a toast table to that the row size of the original table 
doesn't grow excessively. See, for example, 
 

So I suspect what happened is that someone entered some long text values and 
postgres created the toast tables to handle these long strings.

HTH,

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

[postgis-users] where did pg_toast,pg_toast_temp1 come from?

[Robert Buckley]

Hi,

when I left work today these pg_toast tables were not in my database. when I 
looked later ther were.

Can anyone tell me where they came from and why they are automatically created 
in every database?

thanks,

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

Re: [postgis-users] ST_Insersection problem

[G. van Es]

Thanks for the advice. I will definitely look into this. For now I'm going to 
warmup a little in Italy for a few days so I will report my findings in two 
weeks.

Kind regards,

Ge




From: Andrea Peri <>
To: PostGIS Users Discussion 
Sent: Thursday, September 22, 2011 12:02 PM
Subject: Re: [postgis-users] ST_Insersection problem


>Hello Group,
>We are using "POSTGIS="1.5.1" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 
>September 2009" LIBXML="2.7.6" USE_STATS" and having a problem with 
>ST_Intersection().
>Given the following query
>select count(st_intersection(tbl_a.the_geom,tbl_b.the_geom)) from tbl_a, tbl_b;
>will result in this error message
>NOTICE:  TopologyException: found non-noded intersection between LINESTRING 
>(62723.7 426635, 62722.5 426634) and LINESTRING (62723.7 426635, 62726.2 
>426632) at 62723.7 426635
>ERROR: GEOS Intersection() threw an error!
>SQL status:XX000
>
>After the NOTICE message we expect the query to continue but it doesn't.  Both 
>tables have all records st_isvalid='t'.
>
>Does anyone know a solution or workaround for this problem?
>
>Thanks,

Hi,
I give my 2ct. :)
The problemyou report is not really a problem of postgis, but instead is a 
problem of the finite arithmetic use by the pcs and of the methematical 
algorithm used .

I have every time the error you report.
Please notice I run about 10-20 million records of geometry and some geometry 
has also 1 million vertex :)

Is a nightmare.
But this is the beautiful and the hell of the arithmetic finite .

To resolve this you must detect at every step what happened and filter they 
using specific "where" clause or CASE operators.

Is pretty easy,
you will see often it return a Collection, and you get only the lines or the 
polys from that, again
you can filter out the empty geometry.
After this long path you will have a good procedure to clean all the problem of 
a real intersection on a arithmetic finite machine.

Regards,
Andrea Peri.



-- 
-
Andrea Peri
. . . . . . . . . 
qwerty àèìòù
-


___
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] Simple ST_Value(rast, point)

[Andreas Forø Tollefsen]

I managed to get it working.
However, I receive the 'lower bound of FOR loop cannot be null'.

Why does i.e. this query give this error? It does not matter if I exclude or
include no data values, or change the no data value of my raster.
Query:
SELECT foo.gid::integer, 1990, CAST((foo.geomval).val AS integer) as val
FROM
(SELECT a.rid, g.gid, ST_Intersection(a.rast, g.centroid) AS geomval
FROM pop90 a, priogrid_land g WHERE ST_Intersects(a.rast, g.centroid)) AS
foo;

Result:
ERROR:  lower bound of FOR loop cannot be null
CONTEXT:  PL/pgSQL function "_st_intersects" line 96 at FOR with integer
loop variable

** Error **

ERROR: lower bound of FOR loop cannot be null
SQL state: 22004
Context: PL/pgSQL function "_st_intersects" line 96 at FOR with integer loop
variable

Thanks,

Andreas


2011/9/20 Chris Hermansen 

> Hi Andreas,
>
> If you need priogrid.gid (you might want to name it more suggestively) in
> your output table in my example you would leave the table definition as I
> originally stated, and you would join the priogrid and the p90, p95, etc etc
> tables together in each INSERT/SELECT statement:
>
> CREATE TABLE npopgrid (
> priogrid_gid integer,
> year integer,
> pop integer,
> primary key (priogrid_gid, year));
>
> INSERT INTO npopgrid (priogrid_gid, year, pop)
> SELECT priogrid.gid, 90::integer, ST_Value(p90.rast,
> SetSRID(p.centroid,4326))::integer FROM priogrid AS p, pop90 as p90
>
> WHERE ST_Intersects(p90.rast, p.centroid)
> UNION...
>
> in that case it makes sense to make the primary key (priogrid_gid,year)
> because the priogrid.gid itself will re-appear for each year.
>
>
> 2011/9/20 Andreas Forø Tollefsen 
>
>> Hi Chris,
>> Thanks for your suggestions.
>> It does make more sense. However, the gid thing comes from the priogrid
>> table which is the point table.
>>
>> I think I solved the issue with the error.
>> No data for the raster is -3.40282e+38, so excluding no data values makes
>> the query work.
>> But not excluding the no data values makesthe function crash.
>>
>> The population data can be downloaded here if someone wants to replicate.
>>
>> http://sedac.ciesin.columbia.edu/gpw/global.jsp?file=gpwv3&data=pcount&type=wrk&resolut=half&year=90&version=gpw-v3
>>
>> Andreas
>>
>>
>>
>> 2011/9/20 Chris Hermansen 
>>
>>> Andreas, possibly with a more normalized data model you can make this
>>> work.
>>>
>>> Consider a table that looks like
>>>
>>> CREATE TABLE npopgrid (
>>> gid integer,
>>> year integer,
>>> pop integer,
>>> primary key (gid, year));
>>>
>>> Then you can efficiently put data into it like this:
>>>
>>> INSERT INTO npopgrid (gid, year, pop)
>>> SELECT gid, 90::integer, ST_Value(p90.rast,
>>> SetSRID(p.centroid,4326))::integer from p90
>>> UNION
>>> SELECT gid, 95::integer, ST_Value(p95.rast,
>>> SetSRID(p.centroid,4326))::integer from p95
>>> 
>>>
>>> This - to me at least - makes more sense because I assume that p90 has
>>> the population from 90 at every cell in the raster, p95 at every cell in the
>>> raster from 95, etc.  The query the way you structured it originally
>>> requires some kind of table join exercise and you really don't need that if
>>> your data model is normalized like the above.
>>>
>>> In the end I'm not certain about the "gid" thing because I'm not sure
>>> which table you expect it to come from.  If your gid is unique across all of
>>> your tables p90, p95, etc etc then you are ok but I suspect it's not.
>>> Therefore it's good to have the primary key containing the gid and the
>>> year...
>>>
>>> Does this make sense?
>>>
>>> If you really want an unnormalized version of this in the end (think
>>> about that - why would you want such a thing? if you don't fully understand
>>> normalized data models, perhaps this is a good moment to read about it) you
>>> can denormalize it inexpensively from this structure (exercise left to the
>>> reader... :-0)
>>>
>>> 2011/9/19 Andreas Forø Tollefsen 
>>>
 Somehow that result in an error.

 Query:
 DROP TABLE IF EXISTS popgrid;

 SELECT gid,
 ST_Value(p90.rast, SetSRID(p.centroid,4326)) as pop90,
 ST_Value(p95.rast, SetSRID(p.centroid,4326)) as pop95,
 ST_Value(p00.rast, SetSRID(p.centroid,4326)) as pop00,
 ST_Value(p05.rast, SetSRID(p.centroid,4326)) as pop05,
 ST_Value(p10.rast, SetSRID(p.centroid,4326)) as pop10,
 ST_Value(p15.rast, SetSRID(p.centroid,4326)) as pop15
 INTO popgrid
 FROM pop90 p90, pop95 p95, pop00 p00, pop05 p05, pop10 p10, pop15 p15,
 priogrid p
 WHERE ST_Intersects(p90.rast, p.centroid)
 AND ST_Intersects(p95.rast, p.centroid)
 AND ST_Intersects(p00.rast, p.centroid)
 AND ST_Intersects(p05.rast, p.centroid)
 AND ST_Intersects(p10.rast, p.centroid)
 AND ST_Intersects(p15.rast, p.centroid)
 ;

 Result:
 ERROR:  lower bound of FOR loop cannot be null
 CONTEXT:  PL/pgSQL function "_st_intersects" line 96 at FOR with
 integer loop v

Re: [postgis-users] NetCDF to PostGIS

[Andreas Forø Tollefsen]

Hi.
I'm bumping this one back up. Does anyone know the status of this project?
The post on the forum is rather old, and I can't get any newer
information on the implementation of netcdf in PostGIS.

I would love to be able to read NetCDF directly into postgis raster
without using python to read lines and insert into PostGIS:

Andreas

2010/12/9 Jorge Arévalo :
> On Thu, Dec 9, 2010 at 4:01 PM, Andreas Forø Tollefsen
>  wrote:
>> Hi all,
>> Have anyone got any experience to transfer netcdf files into postgis and
>> store these as rasters or point data?
>> Best,
>> Andreas
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> Hi,
>
> This may help you: http://caro-coops.org/bb/viewtopic.php?t=178
>
> Best regards,
>
> --
> Jorge Arévalo
> Internet & Mobilty Division, DEIMOS
> jorge.arev...@deimos-space.com
> http://mobility.grupodeimos.com/
> http://gis4free.wordpress.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] BMP2Postgis

[Sandro Santilli]

On Thu, Sep 22, 2011 at 07:30:22AM -0700, Bob Pawley wrote:
> On Wed, Sep 21, 2011 at 11:28 PM, Sandro Santill wrote:
> > On Wed, Sep 21, 2011 at 11:11:27PM -0700, Bob Pawley wrote:
> > >
> > > Is it at all possible to convert bitmaps to postgis?
> > 
> > You can, with PostGIS 2.0 (raster2pgsql)
> 
> Is there an estimated date for the release of Postgis 2??

End of this year.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] BMP2Postgis

[Bob Pawley]

-Original Message- 
From: Sandro Santilli

Sent: Wednesday, September 21, 2011 11:28 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] BMP2Postgis

On Wed, Sep 21, 2011 at 11:11:27PM -0700, Bob Pawley wrote:

Is it at all possible to convert bitmaps to postgis?

If so, is there an application available? I only need to convert a hundred 
or so images so I don’t want to spend a whole lot of money – none 
preferably.


You can, with PostGIS 2.0 (raster2pgsql)


Is there an estimated date for the release of Postgis 2??

--strk;

 ()   Free GIS & Flash consultant/developer
 /\   http://strk.keybit.net/services.html
___
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

[postgis-users] NO LIST

[javier . estevez . ext]

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

Re: [postgis-users] schemas and postgis data

[Ben Madin]

Robert,

You can get as complex as you like, but one great use of schemas (I think) has 
been to manage backing up data - especially over the internet, another is to 
manage user access at a more granular level.

if you put your static data (ie background maps) into one schema, then when 
dumping you can use the -N flag to avoid dumping that schema. By doing this we 
avoid backing up several hundred megabytes every night, but new research 
location data is backed up daily - only a few megabytes

similarly, for some projects you might have data on users and access controls, 
but when analysing the data this isn't necessary, so putting important (perhaps 
research) data into it's own schema makes it easy to export from a database 
server to a local machine for analysis.

Dont forget to 

ALTER DATABASE mydatabase SET search_path TO mymainschema, myotherschema, 
someotherstuff, public;

cheers

Ben


On 22/09/2011, at 6:17 PM, Robert Buckley wrote:

> I have just read this explaining about how to structure data and functions 
> within postgresql
> http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
> 
>   ..."The "public" schema is where the PostGIS functions and system 
> tables get installed, so if you dump that schema you get all those 
> definitions in your dump. If those definitions are mixed in amongst your 
> data, loading them into a fresh database gets tricky: are the paths to the 
> libraries the same? are there function name clashes? (The 
> utils/postgis_restore.pl script attempts manfully to strip out PostGIS 
> components from a dump file to allow a clean restore, but it is hard to get 
> 100% performance.)
>   If, on the other hand, all your data is neatly separated into its own 
> schema, you can neatly backup just that schema and avoid having PostGIS 
> system information mixed in with your data. That means you can easily restore 
> your data into any version of PostGIS and PostgreSQL that you like. So 
> upgrades are easy easy easy.
> 
> Remember: Store your data in a schema other than "public".
> "
> 
> 
> Basically Paul recommends saving geodata in a different schema to the postgis 
> functions. 
> 
> My questions are...
>   
> 1 "if the data is located in a different schema which does not have the 
> 800 odd postgis functions, are the functions still available to the data?"
> 2 "are cross schema queries allowed?"
> 3 "does it also make sense to seperate non-spatial tables into their own 
> schemas?"
> 
> 
> thanks for any advice,
> 
> Rob
> ___
> 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] copying tables from one schema to another

[Mathieu Basille]

You can use something along that line:

CREATE SCHEMA new_schema;
ALTER TABLE table_name SET SCHEMA new_schema;

(either with pgdamin or with psql directly)

I personally find it easier to set the schema during the importation 
step with shp2psql/raster2psql, e.g. using:


raster2pgsql -r your_raster.tif -t data.your_raster -l 1 -k 64x64 -s 
26919 -I -M -o your_raster.sql


which will directly import 'your_raster' into the schema 'data'. I ran 
into some problems with indexes by altering schemas*, and I now tend to 
favour the direct import into the intended schema (and it's also quicker 
as it involves less code).


Mathieu.


* After moving a given table in a different schema, I couldn't import 
another table with the same name in the public schema because of the 
associated index, and I add to merely drop the table. There is probably 
a solution to this problem, but I couldn't find it and the drop was a 
quick and easy solution for me...



Le 22/09/2011 08:16, Mr. Puneet Kishor a écrit :


On Sep 22, 2011, at 6:29 AM, Robert Buckley wrote:


Hi,

As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis 
data) I have another question.

I had originally put all my data into the public schema. I want to move my geodata to the 
schema "data", so I just used to following command in the pgadmin3 sql editor

create table data.new_table as (select * from public.old_table);



Don't recreate the tables. Just alter the schema. Check the Pg docs for 
changing the schema.




although this works, the schema is not copied..ie the primary key and other 
contraints are missing.

So i have done this

# first create new table and copy schema
create table data.table_2(like table_1 including defaults including constraints 
including indexes);

#then copy data into table_2
insert into data.table2(select * from public.table_1);

Is there a better/quicker/safer way of doing this? possibly with 
pgadmin3?___
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


--

~$ whoami
Mathieu Basille, Post-Doc

~$ locate
Laboratoire d'Écologie Comportementale et de Conservation de la Faune
+ Centre d'Étude de la Forêt
Département de Biologie
Université Laval, Québec

~$ info
http://ase-research.org/basille

~$ fortune
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] copying tables from one schema to another

[Mr. Puneet Kishor]

On Sep 22, 2011, at 6:29 AM, Robert Buckley wrote:

> Hi,
> 
> As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis 
> data) I have another question.
> 
> I had originally put all my data into the public schema. I want to move my 
> geodata to the schema "data", so I just used to following command in the 
> pgadmin3 sql editor
> 
> create table data.new_table as (select * from public.old_table);


Don't recreate the tables. Just alter the schema. Check the Pg docs for 
changing the schema.


> 
> although this works, the schema is not copied..ie the primary key and other 
> contraints are missing.
> 
> So i have done this
> 
> # first create new table and copy schema
> create table data.table_2(like table_1 including defaults including 
> constraints including indexes);
> 
> #then copy data into table_2
> insert into data.table2(select * from public.table_1);
> 
> Is there a better/quicker/safer way of doing this? possibly with 
> pgadmin3?___
> 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

[postgis-users] copying tables from one schema to another

[Robert Buckley]

Hi,

As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis 
data) I have another question.

I had originally put all my data into the public schema. I want to move my 
geodata to the schema "data", so I just used to following command in the 
pgadmin3 sql editor

create table data.new_table as (select * from public.old_table);

although this works, the schema is not copied..ie the primary key and other 
contraints are missing.

So i have done this

# first create new table and copy schema
create table data.table_2(like table_1 including defaults including constraints 
including indexes);

#then copy data into table_2
insert into data.table2(select * from public.table_1);

Is there a better/quicker/safer way of doing this? possibly with pgadmin3?___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] schemas and postgis data

[Szymon Guz]

On 22 September 2011 12:17, Robert Buckley  wrote:

> I have just read this explaining about how to structure data and functions
> within postgresql
> http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
>
> ..."The "public" schema is where the PostGIS functions and system tables
> get installed, so if you dump that schema you get all those definitions in
> your dump. If those definitions are mixed in amongst your data, loading them
> into a fresh database gets tricky: are the paths to the libraries the same?
> are there function name clashes? (The utils/postgis_restore.pl script
> attempts manfully to strip out PostGIS components from a dump file to allow
> a clean restore, but it is hard to get 100% performance.)
> If, on the other hand, all your data is neatly separated into its own
> schema, you can neatly backup *just that schema* and avoid having PostGIS
> system information mixed in with your data. That means you can easily
> restore your data into *any version of PostGIS and PostgreSQL that you
> like*. So upgrades are easy easy easy.
>
> *Remember: Store your data in a schema other than "public".*
> "
>
>
> Basically Paul recommends saving geodata in a different schema to the
> postgis functions.
>
> My questions are...
> 1 "if the data is located in a different schema which does not have the
> 800 odd postgis functions, are the functions still available to the data?"
> 2 "are cross schema queries allowed?"
> 3  "does it also make sense to seperate non-spatial tables into their own
> schemas?"
>
>
> thanks for any advice,
>
> Rob
>
>
>
Hi Robert,
1) yes, functions are available as long as your search_path contains schema
public (which is standard setting)
2) yes, just use the tables with schemaname in the queries, so you can have
tables with the same name in all schemas, just call them using schemaA.A and
schemaB.A.
3) it depends, but of course you can separate the tables. There is no
difference between tables containing columns of PostGIS types and tables
without those.

regards
Szymon

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

[postgis-users] schemas and postgis data

[Robert Buckley]

I have just read this explaining about how to structure data and functions 
within postgresql
http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html


..."The "public" schema is where the PostGIS functions and system tables get 
installed, so if you dump that schema you get all those definitions in your 
dump. If those definitions are mixed in amongst your data, loading them into a 
fresh database gets tricky: are the paths to the libraries the same? are there 
function name clashes? (The utils/postgis_restore.pl script attempts manfully 
to strip out PostGIS components from a dump file to allow a clean restore, but 
it is hard to get 100% performance.)
If, on the other hand, all your data is neatly separated into its own schema, 
you can neatly backup just that schema and avoid having PostGIS system 
information mixed in with your data. That means you can easily restore your 
data into any version of PostGIS and PostgreSQL that you like. So upgrades are 
easy easy easy.
Remember: Store your data in a schema other than "public".
"


Basically Paul recommends saving geodata in a different schema to the postgis 
functions. 

My questions are...
1"if the data is located in a different schema which does not have the 800 odd 
postgis functions, are the functions still available to the data?"
2"are cross schema queries allowed?"
3 "does it also make sense to seperate non-spatial tables into their own 
schemas?"


thanks for any advice,

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

Re: [postgis-users] ST_Insersection problem

[Andrea Peri]

>Hello Group,
>We are using "POSTGIS="1.5.1" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 
>September 2009" LIBXML="2.7.6" USE_STATS" and having a problem with 
>ST_Intersection().
>Given the following query
>select count(st_intersection(tbl_a.the_geom,tbl_b.the_geom)) from tbl_a, tbl_b;
>will result in this error message
>NOTICE:  TopologyException: found non-noded intersection between LINESTRING 
>(62723.7 426635, 62722.5 426634) and LINESTRING (62723.7 426635, 62726.2 
>426632) at 62723.7 426635
>ERROR: GEOS Intersection() threw an error!
>SQL status:XX000
>
>After the NOTICE message we expect the query to continue but it doesn't.  Both 
>tables have all records st_isvalid='t'.
>
>Does anyone know a solution or workaround for this problem?
>
>Thanks,

Hi,
I give my 2ct. :)
The problemyou report is not really a problem of postgis, but instead
is a problem of the finite arithmetic use by the pcs and of the
methematical algorithm used .

I have every time the error you report.
Please notice I run about 10-20 million records of geometry and some
geometry has also 1 million vertex :)

Is a nightmare.
But this is the beautiful and the hell of the arithmetic finite .

To resolve this you must detect at every step what happened and filter
they using specific "where" clause or CASE operators.

Is pretty easy,
you will see often it return a Collection, and you get only the lines
or the polys from that, again
you can filter out the empty geometry.
After this long path you will have a good procedure to clean all the
problem of a real intersection on a arithmetic finite machine.

Regards,
Andrea Peri.



-- 
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Insersection problem

[s...@keybit.net]

On Wed, Sep 21, 2011 at 11:49:22PM -0700, G. van Es wrote:
> Well, omitting hundreds or thousands from the source of over 11 million isn't 
> something i can do. The procedure should run in batch, otherwise i would use 
> Qgis which does the same job in an hour.
> 
> So, i am still looking for a sql solution...

You'll need a scripting language, either within or external to the DB.
Can't do in SQL only with current postgis.

This being a frequently recurring complaint, it may be a good idea 
allowing for a configuration setting or additional arguments to make
topological operations return NULL on robustness issues rather than
throwin an exception.

I could work on that if someone is willing to sponsor the work.

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users