Re: [postgis-users] 2010 Census

[Dan Putler]

Hi Eric,

I'm not aware of any tutorial. Moreover, the redistricting data is just 
now being released. However, I'm in the process of doing this at the 
moment. I'm actually starting with the 2009 5 year summary for the 
American Community Survey (or ACS) at the Block Group and Census Tract 
levels. Really off topic for this list, but the long form was not used 
in the 2010 Census. As a result, there are no housing, income, poverty, 
commuting time, and a host of other variables reported in the 2010 
Census. Instead, this information now is part of the ACS.


We can chat a bit more off list if you are interested.

Dan

On 03/08/2011 06:10 PM, Eric Aspengren wrote:
I know this has likely been covered on this list before. So, pardon if 
this is redundant, I just signed up. I'm looking for a good tutorial 
to get the new 2010 Census data for a state loaded into a PostGIS 
database, including all the recent Tiger files and demographic data.


Is there a good one out there?

--
Eric Aspengren
Data Manager
Planned Parenthood of the Heartland
(402) 478-VOTE
ericas...@gmail.com 


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

[postgis-users] 2010 Census

[Eric Aspengren]

I know this has likely been covered on this list before. So, pardon if this
is redundant, I just signed up. I'm looking for a good tutorial to get the
new 2010 Census data for a state loaded into a PostGIS database, including
all the recent Tiger files and demographic data.

Is there a good one out there?

-- 
Eric Aspengren
Data Manager
Planned Parenthood of the Heartland
(402) 478-VOTE
ericas...@gmail.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Increase query performance

[Nicklas Avén]

Andreas just a thought. 
I would be a little suspicious about the grid. Could it be that your
grid doesn't consist of just 4 corner cells. As I understood from your
other post it was created in python.
I have not experienced it in postgis, but in other software that when
you have played enough with something you think is a very simple geomety
it happens to have a lot of vertex points because of transformations.

Can you have done anything causing the grid to be more complex than
expected? you can check that out with st_npoints.

Maybe you have mentioned, but do you get a proper index-scan.

Have you tried to just do the intersects test without intersection?
If that is very much faster it might be an idea to handle grid cells
contained in polygons separately since they will be intact grid cells. I
don't know if there is such a shortcut in st_intersection. If not, maybe
something to take a look at.

Maybe something like below. Only do the ST_Intersection calculation when
the grid cell intersects the boundary of the cshapeswdate.geom otherwise
if the grid is within cshapeswdate.geom just take the whole grid cell.

Maybe faster, I don't know.


SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate,
enddate, capname, caplong, caplat, col, row, xcoord, ycoord INTO
cshapesgrid1946 FROM priogrid_land, cshapeswdate WHERE
ST_Intersects(priogrid_land.cell, st_boundary(cshapeswdate.geom)) AND
cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear >=1946 AND
cshapeswdate.startdate <= '1946/1/1'

union all

SELECT priogrid_land.cell AS geom, priogrid_land.gid AS divider, gwcode,
gwsyear, gweyear, startdate, enddate, capname, caplong, caplat, col,
row, xcoord, ycoord INTO cshapesgrid1946 FROM priogrid_land,
cshapeswdate WHERE ST_within(priogrid_land.cell, cshapeswdate.geom) AND
cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear >=1946 AND
cshapeswdate.startdate <= '1946/1/1'
;

/Nicklas


On Tue, 2011-03-08 at 16:02 -0500, Paragon Corporation wrote:
> Andreas,
>  
> Try ST_SimplifyPreserveTolerance.  The ST_Simplify often simplifies to
> nothing or close.
>  
> Like I mentioned in the other post, it could be some huge geometries
> causing your problems.  You don't necessarily want to simplify
> everything.
>  
> We usually do a conditional simplify like
>  
> CASE WHEN ST_NPoints(geom) > 3000 THEN
> ST_SimplyfyPreserveTopology(geom,0.1) ELSE geom END
>  
>  
> It probably wouldn't hurt to do a max check on your tables or a count
> to see how many have more than n number of points.
>  
> SELECT MAX(ST_NPoints(geom)) As biggest, COUNT(CASE WHEN
> ST_NPoints(geom) > 3000 THEN 1 ELSE NULL END) as cnt_big_geoms
> FROM yourtable
>  
> To get a sense of the largest geometry you are dealing with.
>  
> Hope that helps,
> Regina
> http://www.postgis.us
> 
> 
> __
> From: Andreas Forø Tollefsen [mailto:andrea...@gmail.com] 
> Sent: Tuesday, March 08, 2011 12:02 PM
> To: PostGIS Users Discussion
> Cc: Paragon Corporation
> Subject: Re: [postgis-users] Increase query performance
> 
> 
> 
> After a suggestion from pgsql_performance i tried with ST_Simplify to
> speed things up. 
> However this gives me a:
> NOTICE: ptarray_simplify returned a <2 pts array
> 
> 
> Then server connection terminates.
> 
> 
> Like this:  
> 
> 
> SELECT ST_Intersection(priogrid_land.cell,
> ST_Simplify(cshapeswdate.geom,0.1)) AS geom, 
> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate,
> enddate, capname, caplong, caplat, col, row, xcoord, ycoord 
> FROM priogrid_land, cshapeswdate WHERE
> ST_Intersects(priogrid_land.cell, ST_Simplify(cshapeswdate.geom,0.1))
> AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear >=1946 AND
> cshapeswdate.startdate <= '1946/1/1';
> 
> 
> 
> 2011/3/8 Andreas Forø Tollefsen 
> Results from the EXPLAIN ANALYZE: 
> 
> 
> "Nested Loop  (cost=0.00..1189.72 rows=3941 width=87790)
> (actual time=7.091..2524830.264 rows=54145 loops=1)"
> "  Join Filter: _st_intersects(priogrid_land.cell,
> cshapeswdate.geom)"
> "  ->  Seq Scan on cshapeswdate  (cost=0.00..16.23 rows=22
> width=87304) (actual time=0.011..0.542 rows=72 loops=1)"
> "Filter: ((gwsyear <= 1946::numeric) AND (gweyear >=
> 1946::numeric) AND (startdate <= '1946-01-01'::date))"
> "  ->  Index Scan using idx_priogrid_land_cell on
> priogrid_land  (cost=0.00..8.29 rows=1 width=486) (actual
> time=3.026..30.152 rows=1338 loops=72)"
> "Index Cond: (priogrid_land.cell &&
> cshapeswdate.geom)"
> "Total runtime: 2524889.630 ms"
> 
> 
> 
> 2011/3/8 Andreas Forø Tollefsen  
> 
> 
> Hi, 
> This query takes about 41 minutes per year. Doing this
> for every year from 194

Re: [postgis-users] Increase query performance

[Paragon Corporation]

Andreas,
 
Try ST_SimplifyPreserveTolerance.  The ST_Simplify often simplifies to
nothing or close.
 
Like I mentioned in the other post, it could be some huge geometries causing
your problems.  You don't necessarily want to simplify everything.
 
We usually do a conditional simplify like
 
CASE WHEN ST_NPoints(geom) > 3000 THEN ST_SimplyfyPreserveTopology(geom,0.1)
ELSE geom END
 
 
It probably wouldn't hurt to do a max check on your tables or a count to see
how many have more than n number of points.
 
SELECT MAX(ST_NPoints(geom)) As biggest, COUNT(CASE WHEN ST_NPoints(geom) >
3000 THEN 1 ELSE NULL END) as cnt_big_geoms
FROM yourtable
 
To get a sense of the largest geometry you are dealing with.
 
Hope that helps,
Regina
http://www.postgis.us

  _  

From: Andreas Forø Tollefsen [mailto:andrea...@gmail.com] 
Sent: Tuesday, March 08, 2011 12:02 PM
To: PostGIS Users Discussion
Cc: Paragon Corporation
Subject: Re: [postgis-users] Increase query performance


After a suggestion from pgsql_performance i tried with ST_Simplify to speed
things up. 
However this gives me a:
NOTICE: ptarray_simplify returned a <2 pts array

Then server connection terminates.


Like this:  

SELECT ST_Intersection(priogrid_land.cell,
ST_Simplify(cshapeswdate.geom,0.1)) AS geom, 
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord 
FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear <=1946 AND
cshapeswdate.gweyear >=1946 AND cshapeswdate.startdate <= '1946/1/1';


2011/3/8 Andreas Forø Tollefsen 


Results from the EXPLAIN ANALYZE: 

"Nested Loop  (cost=0.00..1189.72 rows=3941 width=87790) (actual
time=7.091..2524830.264 rows=54145 loops=1)"
"  Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)"
"  ->  Seq Scan on cshapeswdate  (cost=0.00..16.23 rows=22 width=87304)
(actual time=0.011..0.542 rows=72 loops=1)"
"Filter: ((gwsyear <= 1946::numeric) AND (gweyear >= 1946::numeric)
AND (startdate <= '1946-01-01'::date))"
"  ->  Index Scan using idx_priogrid_land_cell on priogrid_land
(cost=0.00..8.29 rows=1 width=486) (actual time=3.026..30.152 rows=1338
loops=72)"
"Index Cond: (priogrid_land.cell && cshapeswdate.geom)"
"Total runtime: 2524889.630 ms"


2011/3/8 Andreas Forø Tollefsen  


Hi, 
This query takes about 41 minutes per year. Doing this for every year from
1946 to 2008 takes a lot of time.
The grid consists of 64818 cell polygons, while the country table has about
210 polygons.

I will add indexes on year and date and try again. An do an explain analyze.

Cheers,
Andreas


2011/3/8 Paragon Corporation 


Andreas,
I don't see anything glaringly wrong with your query, but hard to tell
without seeing an explain plan or what you mean by takes a lot of time.  Is
a lot of time minutes, hours, days?
How many records are we talking about here?  What's the max number of points
you have in any geometry.  Often times its just one huge mega geometry with
a 100,000 points or more slowing everything down.
 
Do you have indexes on your year fields and date columns?
 
Hope that helps,
Regina 
http://www.postgis.us
 

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas
Forø Tollefsen
Sent: Tuesday, March 08, 2011 4:15 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Increase query performance


Hi all, 

Another question on postgis query performance. I did some discussion on the
pgsql performance list on how i could increase the performance on my server.
I did manage to increase the transactions per second, but came to the
conclusion that this did not help the ST_Intersection query which i was
trying to speed up.

Any suggestions on how to speed up this query? Basically i want to create an
intersection between a vector grid and country shapefiles. Then calculate
the area of all the polygons in the intersected table, and finally selecting
the country code for each cell which represents the largest area within each
cell. I does work as it is, but i would like to increase the speed. The
query which takes a lot of time is the ST_Intersection.

"PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"

"POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
2009" LIBXML="2.7.7" USE_STATS"


DROP TABLE IF EXISTS cshapesgrid1946;

SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
>=1946 AND cshapeswdate.startdate <= '1946/1/1';

ALTER TABLE cshapesgrid1946 ADD COLUMN area float;

Re: [postgis-users] a couple of procedural questions for populating tables programmatically

[Mike Toews]

On 9 March 2011 03:07, Mr. Puneet Kishor  wrote:
>
> I have a couple of procedural questions. I am populating my table with point 
> and cell geometries programmatically. First question: When I INSERT values 
> other than the geometry in rows and then UPDATE the geometry in a second 
> process, everything works --
>
>   $sth = $dbh->prepare(qq{INSERT INTO base.cells (x, y) VALUES (?, ?)});
>
>   for (@a) {
>       $sth->execute($a[0], $a[b]);
>   }
>
>   $sth = $dbh->prepare(qq{
>       UPDATE base.new_cells
>       SET
>           the_point = GeomFromText('POINT(' || x || ' ' || y || ')', 2163),
>           the_cell  = GeomFromText(
>               'POLYGON((' ||
>               (x - $csmid) || ' ' || (y - $csmid) || ',' ||
>               (x + $csmid) || ' ' || (y - $csmid) || ',' ||
>               (x + $csmid) || ' ' || (y + $csmid) || ',' ||
>               (x - $csmid) || ' ' || (y + $csmid) || ',' ||
>               (x - $csmid) || ' ' || (y - $csmid) ||  '))',
>               2163
>           )
>   });
>   $sth->execute;
>
> However, if I try to do the above in one attempt like so --
>
>   $sth = $dbh->prepare(qq{INSERT INTO base.cells (x, y, the_point, the_cell) 
> VALUES (?, ?, ?, ?)});
>
>   for (@a) {
>       $sth->execute(
>           $a[0],
>           $a[b],
>           GeomFromText('POINT(' || $a[0] || ' ' || $a[y] || ')', 2163),

If you want to avoid precision loss from double->text conversions, try
setting the_point constructor with:
ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163)
(or is $a[y] really $a[b]? Why is $a[0] not $a[x]? I'm not sure)

>           GeomFromText(
>               'POLYGON((' ||
>               (x - $csmid) || ' ' || (y - $csmid) || ',' ||
>               (x + $csmid) || ' ' || (y - $csmid) || ',' ||
>               (x + $csmid) || ' ' || (y + $csmid) || ',' ||
>               (x - $csmid) || ' ' || (y + $csmid) || ',' ||
>               (x - $csmid) || ' ' || (y - $csmid) ||  '))',
>               2163

There is easily a problem somewhere here. I'm not sure how exactly x,
y, and $csmid are used here, but they can't refer to the SQL columns
"x" or "y" yet, since this is an INSERT statement. A similar binary
method to above can be combined with ST_Expand:

ST_Expand(ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163), $csmid)

>
> I get an error saying the method GeomFromText doesn't exist. Would like to 
> understand the reason behind this.

Your full error message possibly says something like
"ERROR:  function geomfromtext(unknown) does not exist"
because the datatype your are passing to GeomFromText is not text, and
another function for any other datatype does not exist.

> Second question: I am doing the following to set the SRID (besides specifying 
> it in the UPDATE step above).
>
>   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, 
> f_table_name, f_geometry_column, coord_dimension, srid, type)
>   VALUES ('', 'base', 'cells', 'the_point', 2, 2163, 'POINT');
>   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, 
> f_table_name, f_geometry_column, coord_dimension, srid, type)
>   VALUES ('', 'base', 'cells', 'the_cell', 2, 2163, 'POLYGON');
>
> Is the above correct and sufficient?

Looks correct, but it only needs to be done once, when you make the
table/columns. Don't insert this after each UPDATE step above.

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

Re: [postgis-users] Increase query performance

[Andreas Forø Tollefsen]

After a suggestion from pgsql_performance i tried with ST_Simplify to speed
things up.
However this gives me a:
NOTICE: ptarray_simplify returned a <2 pts array

Then server connection terminates.

Like this:

SELECT ST_Intersection(priogrid_land.cell,
ST_Simplify(cshapeswdate.geom,0.1)) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord
FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear <=1946 AND
cshapeswdate.gweyear >=1946 AND cshapeswdate.startdate <= '1946/1/1';


2011/3/8 Andreas Forø Tollefsen 

> Results from the EXPLAIN ANALYZE:
>
> "Nested Loop  (cost=0.00..1189.72 rows=3941 width=87790) (actual
> time=7.091..2524830.264 rows=54145 loops=1)"
> "  Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)"
> "  ->  Seq Scan on cshapeswdate  (cost=0.00..16.23 rows=22 width=87304)
> (actual time=0.011..0.542 rows=72 loops=1)"
> "Filter: ((gwsyear <= 1946::numeric) AND (gweyear >= 1946::numeric)
> AND (startdate <= '1946-01-01'::date))"
> "  ->  Index Scan using idx_priogrid_land_cell on priogrid_land
>  (cost=0.00..8.29 rows=1 width=486) (actual time=3.026..30.152 rows=1338
> loops=72)"
> "Index Cond: (priogrid_land.cell && cshapeswdate.geom)"
> "Total runtime: 2524889.630 ms"
>
>
> 2011/3/8 Andreas Forø Tollefsen 
>
> Hi,
>> This query takes about 41 minutes per year. Doing this for every year from
>> 1946 to 2008 takes a lot of time.
>> The grid consists of 64818 cell polygons, while the country table has
>> about 210 polygons.
>>
>> I will add indexes on year and date and try again. An do an explain
>> analyze.
>>
>> Cheers,
>> Andreas
>>
>> 2011/3/8 Paragon Corporation 
>>
>>>  Andreas,
>>> I don't see anything glaringly wrong with your query, but hard to tell
>>> without seeing an explain plan or what you mean by takes a lot of time.  Is
>>> a lot of time minutes, hours, days?
>>> How many records are we talking about here?  What's the max number of
>>> points you have in any geometry.  Often times its just one huge mega
>>> geometry with a 100,000 points or more slowing everything down.
>>>
>>> Do you have indexes on your year fields and date columns?
>>>
>>> Hope that helps,
>>> Regina
>>> http://www.postgis.us
>>>
>>>
>>>  --
>>> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
>>> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas
>>> Forø Tollefsen
>>> *Sent:* Tuesday, March 08, 2011 4:15 AM
>>> *To:* PostGIS Users Discussion
>>> *Subject:* [postgis-users] Increase query performance
>>>
>>> Hi all,
>>>
>>> Another question on postgis query performance. I did some discussion on
>>> the pgsql performance list on how i could increase the performance on my
>>> server.
>>> I did manage to increase the transactions per second, but came to the
>>> conclusion that this did not help the ST_Intersection query which i was
>>> trying to speed up.
>>>
>>> Any suggestions on how to speed up this query? Basically i want to create
>>> an intersection between a vector grid and country shapefiles. Then calculate
>>> the area of all the polygons in the intersected table, and finally selecting
>>> the country code for each cell which represents the largest area within each
>>> cell. I does work as it is, but i would like to increase the speed. The
>>> query which takes a lot of time is the ST_Intersection.
>>>
>>> "PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
>>> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"
>>>
>>> "POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23
>>> September 2009" LIBXML="2.7.7" USE_STATS"
>>>
>>>
>>>  DROP TABLE IF EXISTS cshapesgrid1946;
>>>
>>> SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
>>> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
>>> capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
>>> priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
>>> cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
>>> >=1946 AND cshapeswdate.startdate <= '1946/1/1';
>>>
>>> ALTER TABLE cshapesgrid1946 ADD COLUMN area float;
>>>
>>> UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);
>>>
>>> DROP TABLE IF EXISTS pg1946;
>>>
>>> SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
>>> MAX(area) FROM cshapesgrid1946 b GROUP BY divider);
>>>
>>> CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);
>>>
>>>
>>> Best,
>>> Andreas
>>>
>>> ___
>>> 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.n

Re: [postgis-users] newbie prob: PostgreSQL server will not connect so PostGIS installation fails with dbcreate

[Sean]

Did you start PostgreSQL?

It should be under:
Start -> PostgreSQL 9.x.x -> Start database

Look up how to have it start as a service so you don't have to
manually start it.

  Sean

On Mar 8, 8:11 am, "Florian Reimer"  wrote:
> Hi,
>
> I am totally new to PostgreSQL & PostGIS so I excuse that my question is 
> lame, but the hint in the pgAdmin III help did not work out. As I think I 
> tried to install with all default settings, this might occur to other newbies 
> too:
>
> I installed PostgreSQL 9.0 under Windos98 with the installer, fine. I left 
> the default settings for server/account creation: postgres, localhost, 5432.
>
> In the pgAdmin III prog (Windows GUI) I dont manage to connect the server, 
> that's why my PostGIS 1.5 installation also fails.
>
> I read in pgAdmin III help that postgresql.conf setting must be:
> # - Connection Settings -
>
> listen_addresses = '*'
>
> ...AND this was its setting already by default.
>
> So why cant I connect the server? Where do I want to connect anyway? - as 
> actually I just want to install PostGIS on a machine to have a look at it.
>
> Anyone please with some newbie-help how to connect the PostgresSQL server so 
> I can install and launch PostGIS? *offering some virtual cookies*
>
> Thanks a lot and cheers
>
> florian
> --
> Schon gehört? GMX hat einen genialen Phishing-Filter in die
> Toolbar eingebaut!http://www.gmx.net/de/go/toolbar
> ___
> postgis-users mailing list
> postgis-us...@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] Shared memory extesion on a a Mac for PostgreSQL/PostGIS

[Scott Brunza]

I've several systems with 8-24 GB of RAM running postgres and postgis.  I built 
postgres as a 64-bit executable to take advantage of the larger address space.  
As a rule for databases, they are memory hungry.  I generally follow:  
effective cache size, 50% RAM; shared_buffers, 25% RAM.  There are other PG 
tuning parameters available in performance docs/discussions on the web.

With only 2GB of RAM, the bit-width of postgres shouldn't matter, and your 
setting of 1.5 GB shmmax seems a little too large.  Try keeping it below 1.25 
GB, or even 1 GB.  Since you said this is only a prototype, performance 
shouldn't be a top priority.  You also don't want to starve the OS of 
resources.  Consider starting with your postgres setting half or even a quarter 
of what I stated above.  Also, consider lowering the max_connections setting; 
each connection requires more shared memory.  This way you can also limit the 
size of your kernel resources.

You can also dynamically change the kernel parameters with `sysctl -w 
PARAMETER=VALUE` from the command line to find a setting you're comfortable 
with before committing it to /etc/sysctl.conf.  The only reason to have large 
memory space for postgres is to store as much of the database as possible in 
memory, i.e., faster access and less/no swapping.

As for the pgAdmin question, it's a good tool, but it ultimately comes down to 
what you're comfortable with.  I prefer the command line.

Hope that clears things up at least a bit.
-B-


--- Let us all bask in television's warm glowing warming glow ---
Scott Brunza   860.326.3637 scot...@sonalysts.com

This e-mail and any files transmitted with it may be  proprietary
and are  intended solely for the use of the  individual or entity
to whom they are addressed.  If you have received this  e-mail in
error please notify the sender.

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

Re: [postgis-users] Creating a grid purely in SQL

[Randall, Eric]

Hi Puneet,

This is typically how I've done it.  This example query builds a .25
degree grid around Pennsylvania.  There are probably better ways


select row, col, geom
from
(
select yseries+1 as row,xseries+1 as
col,st_setsrid(st_translate(geom,0,yseries * .25),4326) as geom
from
(
select xseries, generate_series(0,15,1) as yseries,
st_translate(geom,xseries * .25,0) as geom
from
(
select generate_series(0,27,1) as xseries,
st_envelope(st_makeline(st_pointn(st_boundary(geom),1),
st_makepoint(st_x(st_pointn(st_boundary(geom),1)) + .25, 
st_y(st_pointn(st_boundary(geom),1)) + .25))) as geom  
from
(
select
st_envelope(st_makebox2d(st_makepoint(-81,39),st_makepoint(-74,43))) as
geom
) as g1
) as g2
) as g3
) as g4


-Eric


...though the mischief arising from the study of words is
prodigious, we must not consider it as the only cause of darkening
the splendours of Truth, and obstructing the free diffusion of her
light.  Different manners and philosophies have equally contributed
to banish the goddess from our realms, and to render our eyes
offended with her celestial light. Hence we must not wonder that,
being indignant at the change, and perceiving the empire of
ignorance rising to unbounded dominion, she has retired from the
spreading darkness, and concealed herself in the tranquil and
divinely lucid regions of mind.   -Thomas Taylor

-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mr.
Puneet Kishor
Sent: Monday, March 07, 2011 11:13 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Creating a grid purely in SQL

Given a top-left starting point [ulx, uly], and a cell width 'w' and
height 'h', is it possible to create a table entirely in SQL populated
with rows increasing from left to right up to X and top to bottom up to
Y. The table schema would be something like --

CREATE TABLE cells (
  cell_id INTEGER NOT NULL,
  xmid DOUBLE PRECISION,
  ymid DOUBLE PRECISION,
  the_geom GEOMETRY,
  CONSTRAINT cells_pkey PRIMARY KEY (cell_id) );

where xmid = (xmin + xmax) / 2 and ymid = (ymin + ymax) / 2, [xmin,
ymin, xmax, ymax] being the corners of each cell.

A bonus question -- is it possible to store two geometry columns in one
table? For example, if I wanted to store the geometry for both the
center points [xmin, ymid] as well as the box [xmin, ymin, xmax, ymax],
would that be possible? Would that even be recommended (for example, to
speed up queries/drawing, etc.).

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] a couple of procedural questions for populating tables programmatically

[Mr. Puneet Kishor]

I have a couple of procedural questions. I am populating my table with point 
and cell geometries programmatically. First question: When I INSERT values 
other than the geometry in rows and then UPDATE the geometry in a second 
process, everything works --

   $sth = $dbh->prepare(qq{INSERT INTO base.cells (x, y) VALUES (?, ?)});

   for (@a) {
   $sth->execute($a[0], $a[b]);
   }

   $sth = $dbh->prepare(qq{
   UPDATE base.new_cells 
   SET 
   the_point = GeomFromText('POINT(' || x || ' ' || y || ')', 2163),
   the_cell  = GeomFromText(
   'POLYGON((' || 
   (x - $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y - $csmid) ||  '))', 
   2163
   )
   });
   $sth->execute;

However, if I try to do the above in one attempt like so --

   $sth = $dbh->prepare(qq{INSERT INTO base.cells (x, y, the_point, the_cell) 
VALUES (?, ?, ?, ?)});

   for (@a) {
   $sth->execute(
   $a[0], 
   $a[b], 
   GeomFromText('POINT(' || $a[0] || ' ' || $a[y] || ')', 2163),
   GeomFromText(
   'POLYGON((' || 
   (x - $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y - $csmid) ||  '))', 
   2163
   )
   );
   }

I get an error saying the method GeomFromText doesn't exist. Would like to 
understand the reason behind this.

Second question: I am doing the following to set the SRID (besides specifying 
it in the UPDATE step above).

   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, 
f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_point', 2, 2163, 'POINT');
   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, 
f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_cell', 2, 2163, 'POLYGON');

Is the above correct and sufficient?

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

[postgis-users] newbie prob: PostgreSQL server will not connect so PostGIS installation fails with dbcreate

[Florian Reimer]

Hi,

I am totally new to PostgreSQL & PostGIS so I excuse that my question is lame, 
but the hint in the pgAdmin III help did not work out. As I think I tried to 
install with all default settings, this might occur to other newbies too:

I installed PostgreSQL 9.0 under Windos98 with the installer, fine. I left the 
default settings for server/account creation: postgres, localhost, 5432.

In the pgAdmin III prog (Windows GUI) I dont manage to connect the server, 
that's why my PostGIS 1.5 installation also fails.

I read in pgAdmin III help that postgresql.conf setting must be:
# - Connection Settings -

listen_addresses = '*'

...AND this was its setting already by default.

So why cant I connect the server? Where do I want to connect anyway? - as 
actually I just want to install PostGIS on a machine to have a look at it.

Anyone please with some newbie-help how to connect the PostgresSQL server so I 
can install and launch PostGIS? *offering some virtual cookies*

Thanks a lot and cheers

florian
-- 
Schon gehört? GMX hat einen genialen Phishing-Filter in die
Toolbar eingebaut! http://www.gmx.net/de/go/toolbar
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Increase query performance

[Andreas Forø Tollefsen]

Results from the EXPLAIN ANALYZE:

"Nested Loop  (cost=0.00..1189.72 rows=3941 width=87790) (actual
time=7.091..2524830.264 rows=54145 loops=1)"
"  Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)"
"  ->  Seq Scan on cshapeswdate  (cost=0.00..16.23 rows=22 width=87304)
(actual time=0.011..0.542 rows=72 loops=1)"
"Filter: ((gwsyear <= 1946::numeric) AND (gweyear >= 1946::numeric)
AND (startdate <= '1946-01-01'::date))"
"  ->  Index Scan using idx_priogrid_land_cell on priogrid_land
 (cost=0.00..8.29 rows=1 width=486) (actual time=3.026..30.152 rows=1338
loops=72)"
"Index Cond: (priogrid_land.cell && cshapeswdate.geom)"
"Total runtime: 2524889.630 ms"


2011/3/8 Andreas Forø Tollefsen 

> Hi,
> This query takes about 41 minutes per year. Doing this for every year from
> 1946 to 2008 takes a lot of time.
> The grid consists of 64818 cell polygons, while the country table has about
> 210 polygons.
>
> I will add indexes on year and date and try again. An do an explain
> analyze.
>
> Cheers,
> Andreas
>
> 2011/3/8 Paragon Corporation 
>
>>  Andreas,
>> I don't see anything glaringly wrong with your query, but hard to tell
>> without seeing an explain plan or what you mean by takes a lot of time.  Is
>> a lot of time minutes, hours, days?
>> How many records are we talking about here?  What's the max number of
>> points you have in any geometry.  Often times its just one huge mega
>> geometry with a 100,000 points or more slowing everything down.
>>
>> Do you have indexes on your year fields and date columns?
>>
>> Hope that helps,
>> Regina
>> http://www.postgis.us
>>
>>
>>  --
>> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
>> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas
>> Forø Tollefsen
>> *Sent:* Tuesday, March 08, 2011 4:15 AM
>> *To:* PostGIS Users Discussion
>> *Subject:* [postgis-users] Increase query performance
>>
>> Hi all,
>>
>> Another question on postgis query performance. I did some discussion on
>> the pgsql performance list on how i could increase the performance on my
>> server.
>> I did manage to increase the transactions per second, but came to the
>> conclusion that this did not help the ST_Intersection query which i was
>> trying to speed up.
>>
>> Any suggestions on how to speed up this query? Basically i want to create
>> an intersection between a vector grid and country shapefiles. Then calculate
>> the area of all the polygons in the intersected table, and finally selecting
>> the country code for each cell which represents the largest area within each
>> cell. I does work as it is, but i would like to increase the speed. The
>> query which takes a lot of time is the ST_Intersection.
>>
>> "PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
>> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"
>>
>> "POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
>> 2009" LIBXML="2.7.7" USE_STATS"
>>
>>
>>  DROP TABLE IF EXISTS cshapesgrid1946;
>>
>> SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
>> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
>> capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
>> priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
>> cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
>> >=1946 AND cshapeswdate.startdate <= '1946/1/1';
>>
>> ALTER TABLE cshapesgrid1946 ADD COLUMN area float;
>>
>> UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);
>>
>> DROP TABLE IF EXISTS pg1946;
>>
>> SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
>> MAX(area) FROM cshapesgrid1946 b GROUP BY divider);
>>
>> CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);
>>
>>
>> Best,
>> Andreas
>>
>> ___
>> 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] images in postgresql

[Charles Galpin]

Why put the images in the database at all? If you leave them on disk and put a 
relative path into postgres, you can host them anywhere using apache and the 
client can fetch them via url.

charles

On Mar 8, 2011, at 6:32 AM, Paragon Corporation wrote:

> 300 is nothing.  For simplicity especially if it is a 1 to 1 relationship, I 
> would keep it in a single table.
>  
> If you do plan to have multiple pictures per turbine like different side 
> views, then you would keep the pictures in a separate table.
>  
> What Leo was talking about when he said painful was if you are updating 
> millions of records at once.  One record here and there or even 500 records 
> at once is not that big of a deal.  All that would be completed in a flip of 
> an eyelid.
>  
> I would also use bytea type for picture storage than OID (LOID).  I think the 
> non-bytea way is considered deprecated these days and suffers from the 
> problem that you have to delete the data separately from the record otherwise 
> you get orphaned objects.  I think the bytea way is also easier for most 
> applications to read.
>  
> The Oversized-Attribute Storage Technique (TOAST) is a side topic -- really a 
> behind the scenes implementation detail that PostgreSQL uses to get over its 
> 8kb page size limit.  Has nothing to do with data type choice.  You don't 
> usually need to think about it much whether your data is using TOAST or not 
> except when really bad things happen like one of the toast tables becomes 
> corrupt. (which usually signals disk failure anyway).
>  
> Hope that helps,
> Regina
> http://www.postgis.us
>  
> 
> From: postgis-users-boun...@postgis.refractions.net 
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert 
> Buckley
> Sent: Tuesday, March 08, 2011 3:20 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] images in postgresql
> 
> Thanks for all the replies.
> 
> My Wind turbine table will only have around 300 - maximum 500 points. The 
> photos shouldn´t change until either a turbine vanishes or gets repowered 
> (ie. upgraded).
> 
> I am pretty new to postgis so when you all start talking about TOAST I start 
> thinking about food rather than data formats. TOAST, Large blob, OID...how do 
> I decide? As the images will be (hopefully) displayed over the web and they 
> shouldn´t be more than 50 kb each. They are simply there to display a nice 
> picture when someonw clicks on the map. 
> 
> The table will however be updated with new turbines and we will certainly 
> find errors so that some will have to be deleted or moved.
> 
> Would it be easier to keep them all in one table rather than in separate 
> tables?
> 
> yours,
> 
> Rob
> 
> 
> Von: Paragon Corporation 
> An: PostGIS Users Discussion 
> Gesendet: Dienstag, den 8. März 2011, 6:00:00 Uhr
> Betreff: Re: [postgis-users] images in postgresql
> 
> Ben,
>  
> My understanding is the same (as long as you don't select the column that is) 
> otherwise has to be detoasted. As I recall, I think a small bit will be 
> stored and then the rest that doesn't fit into (I can't recall maximum 
> space), gets chunked into toast records.
>  
> Its true for most of the databases I've worked with - e.g. large text or 
> blobs just the pointer is stored in the main table, except PostgreSQL makes 
> this decision conditionally on size and other databases make it beforehand 
> based on data type.
>  
> However -- UPDATES will be painful I think because even though the data is 
> toasted, PostgreSQL will still create an MVCC copy of the whole record when 
> doing updates and slushing around big pictures and geometries can be painful. 
>  So if your other wind turbine info gets changed more often than the photos, 
> I would keep them separate.
>  
>  
> Leo
> http://www.postgis.us
>  
> 
> From: postgis-users-boun...@postgis.refractions.net 
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin
> Sent: Monday, March 07, 2011 8:31 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] images in postgresql
> 
> Robert,
> 
> On 06/03/2011, at 4:28 PM, Robert Buckley wrote:
> 
>> The windturbine table exists in EPSG:4326. I made a seperate table for the 
>> images because I didn´t wan´t to blow the size of the wind turbine table out 
>> of proportion and jeopardize performance.
> 
> My understanding - and if I'm wrong I need to know(!) - is that the sort of 
> data you are talking about (large geometries or blobs - for your pictures) 
> are not stored in the primary table, but in associated storage space, known 
> as TOAST tables. 
> 
> This has important implications for indexing, but is brilliant because the 
> content of these data fields does not directly impact on the number of pages 
> that the table takes, hence rapid searching is still possible.
> 
> cheers
> 
> Ben
> 
> 
> 
> 
>> 
>> I am making a simple application to show wind turbines as wms and I wanted 
>> to show the turbine in a popup. I´m not 

Re: [postgis-users] Increase query performance

[Andreas Forø Tollefsen]

Hi,
This query takes about 41 minutes per year. Doing this for every year from
1946 to 2008 takes a lot of time.
The grid consists of 64818 cell polygons, while the country table has about
210 polygons.

I will add indexes on year and date and try again. An do an explain analyze.

Cheers,
Andreas

2011/3/8 Paragon Corporation 

>  Andreas,
> I don't see anything glaringly wrong with your query, but hard to tell
> without seeing an explain plan or what you mean by takes a lot of time.  Is
> a lot of time minutes, hours, days?
> How many records are we talking about here?  What's the max number of
> points you have in any geometry.  Often times its just one huge mega
> geometry with a 100,000 points or more slowing everything down.
>
> Do you have indexes on your year fields and date columns?
>
> Hope that helps,
> Regina
> http://www.postgis.us
>
>
>  --
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas Forø
> Tollefsen
> *Sent:* Tuesday, March 08, 2011 4:15 AM
> *To:* PostGIS Users Discussion
> *Subject:* [postgis-users] Increase query performance
>
> Hi all,
>
> Another question on postgis query performance. I did some discussion on the
> pgsql performance list on how i could increase the performance on my server.
> I did manage to increase the transactions per second, but came to the
> conclusion that this did not help the ST_Intersection query which i was
> trying to speed up.
>
> Any suggestions on how to speed up this query? Basically i want to create
> an intersection between a vector grid and country shapefiles. Then calculate
> the area of all the polygons in the intersected table, and finally selecting
> the country code for each cell which represents the largest area within each
> cell. I does work as it is, but i would like to increase the speed. The
> query which takes a lot of time is the ST_Intersection.
>
> "PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"
>
> "POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
> 2009" LIBXML="2.7.7" USE_STATS"
>
>
>  DROP TABLE IF EXISTS cshapesgrid1946;
>
> SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
> priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
> capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
> priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
> cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
> >=1946 AND cshapeswdate.startdate <= '1946/1/1';
>
> ALTER TABLE cshapesgrid1946 ADD COLUMN area float;
>
> UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);
>
> DROP TABLE IF EXISTS pg1946;
>
> SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
> MAX(area) FROM cshapesgrid1946 b GROUP BY divider);
>
> CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);
>
>
> Best,
> Andreas
>
> ___
> 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] Create Voronoi Diagrams

[Greg Williamson]

%3% is looking for anything for a "3" in it -- similar to *3* so this would 
find 
every row for BOSTON with grades like .3, .03, 3.0, 1.3, 32, etc. that also 
have 
a type of "pub" ...

As to why the "WHERE" clause?

Perhaps this example is not trying to create a Voroni diagram for their entire 
database, but only for a relatively small number of rows.

Trying to create a Voroni diagram for a large data set will be slow and might 
exceed memory or other system resources, so unless your database is small, I'd 
try a subset of data, either limited by type or place name, or by a bounding 
box.

That said, I haven't used the referred function so I may be misleading with the 
best of intentions. :-}

HTH,

Greg Williamson







From: ΑΠΟΣΤΟΛΟΣ ΛΕΛΕΝΤΖΗΣ 
To: PostGIS Users Discussion 
Sent: Tue, March 8, 2011 2:50:07 AM
Subject: [postgis-users] Create Voronoi Diagrams

Hello everybody

For another time, i ask your help and your experience!!
Specifically, i want to create voronoi diagrams with postgis, and i found the 
following intersting link
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
So, i have installed the pl/r language and i have run the voronoi function.
But, i don't understand what kind of my data i have to place, at the below line 
code
WHERE town = ''BOSTON'' AND grades LIKE ''%3%'' AND type = ''PUB''
why the writer use a WHERE statement ?? 
what does mean the variable "%3%" ??

Thankss



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

Re: [postgis-users] Increase query performance

[Paragon Corporation]

Andreas,
I don't see anything glaringly wrong with your query, but hard to tell
without seeing an explain plan or what you mean by takes a lot of time.  Is
a lot of time minutes, hours, days?
How many records are we talking about here?  What's the max number of points
you have in any geometry.  Often times its just one huge mega geometry with
a 100,000 points or more slowing everything down.
 
Do you have indexes on your year fields and date columns?
 
Hope that helps,
Regina 
http://www.postgis.us
 

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas
Forø Tollefsen
Sent: Tuesday, March 08, 2011 4:15 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Increase query performance


Hi all, 

Another question on postgis query performance. I did some discussion on the
pgsql performance list on how i could increase the performance on my server.
I did manage to increase the transactions per second, but came to the
conclusion that this did not help the ST_Intersection query which i was
trying to speed up.

Any suggestions on how to speed up this query? Basically i want to create an
intersection between a vector grid and country shapefiles. Then calculate
the area of all the polygons in the intersected table, and finally selecting
the country code for each cell which represents the largest area within each
cell. I does work as it is, but i would like to increase the speed. The
query which takes a lot of time is the ST_Intersection.

"PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"

"POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
2009" LIBXML="2.7.7" USE_STATS"


DROP TABLE IF EXISTS cshapesgrid1946;

SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
>=1946 AND cshapeswdate.startdate <= '1946/1/1';

ALTER TABLE cshapesgrid1946 ADD COLUMN area float;

UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);

DROP TABLE IF EXISTS pg1946;

SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
MAX(area) FROM cshapesgrid1946 b GROUP BY divider);

CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);


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

Re: [postgis-users] images in postgresql

[Paragon Corporation]

300 is nothing.  For simplicity especially if it is a 1 to 1 relationship, I
would keep it in a single table.
 
If you do plan to have multiple pictures per turbine like different side
views, then you would keep the pictures in a separate table.
 
What Leo was talking about when he said painful was if you are updating
millions of records at once.  One record here and there or even 500 records
at once is not that big of a deal.  All that would be completed in a flip of
an eyelid.
 
I would also use bytea type for picture storage than OID (LOID).  I think
the non-bytea way is considered deprecated these days and suffers from the
problem that you have to delete the data separately from the record
otherwise you get orphaned objects.  I think the bytea way is also easier
for most applications to read.
 
The Oversized-Attribute Storage Technique (TOAST) is a side topic -- really
a behind the scenes implementation detail that PostgreSQL uses to get over
its 8kb page size limit.  Has nothing to do with data type choice.  You
don't usually need to think about it much whether your data is using TOAST
or not except when really bad things happen like one of the toast tables
becomes corrupt. (which usually signals disk failure anyway).
 
Hope that helps,
Regina
http://www.postgis.us
 

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Robert
Buckley
Sent: Tuesday, March 08, 2011 3:20 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] images in postgresql


Thanks for all the replies.

My Wind turbine table will only have around 300 - maximum 500 points. The
photos shouldn´t change until either a turbine vanishes or gets repowered
(ie. upgraded).

I am pretty new to postgis so when you all start talking about TOAST I start
thinking about food rather than data formats. TOAST, Large blob, OID...how
do I decide? As the images will be (hopefully) displayed over the web and
they shouldn´t be more than 50 kb each. They are simply there to display a
nice picture when someonw clicks on the map. 

The table will however be updated with new turbines and we will certainly
find errors so that some will have to be deleted or moved.

Would it be easier to keep them all in one table rather than in separate
tables?

yours,

Rob



  _  

Von: Paragon Corporation 
An: PostGIS Users Discussion 
Gesendet: Dienstag, den 8. März 2011, 6:00:00 Uhr
Betreff: Re: [postgis-users] images in postgresql


Ben,
 
My understanding is the same (as long as you don't select the column that
is) otherwise has to be detoasted. As I recall, I think a small bit will be
stored and then the rest that doesn't fit into (I can't recall maximum
space), gets chunked into toast records.
 
Its true for most of the databases I've worked with - e.g. large text or
blobs just the pointer is stored in the main table, except PostgreSQL makes
this decision conditionally on size and other databases make it beforehand
based on data type.
 
However -- UPDATES will be painful I think because even though the data is
toasted, PostgreSQL will still create an MVCC copy of the whole record when
doing updates and slushing around big pictures and geometries can be
painful.  So if your other wind turbine info gets changed more often than
the photos, I would keep them separate.
 
 
Leo
http://www.postgis.us
 

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben
Madin
Sent: Monday, March 07, 2011 8:31 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] images in postgresql


Robert, 

On 06/03/2011, at 4:28 PM, Robert Buckley wrote:



The windturbine table exists in EPSG:4326. I made a seperate table for the
images because I didn´t wan´t to blow the size of the wind turbine table out
of proportion and jeopardize performance.



My understanding - and if I'm wrong I need to know(!) - is that the sort of
data you are talking about (large geometries or blobs - for your pictures)
are not stored in the primary table, but in associated storage space, known
as TOAST tables. 

This has important implications for indexing, but is brilliant because the
content of these data fields does not directly impact on the number of pages
that the table takes, hence rapid searching is still possible.

cheers

Ben







I am making a simple application to show wind turbines as wms and I wanted
to show the turbine in a popup. I´m not sure how to get the popup to display
though.

Any examples?
Thanks,

Rob




  _  

Von: Paragon Corporation 
An: PostGIS Users Discussion 
Gesendet: Samstag, den 5. März 2011, 18:21:49 Uhr
Betreff: Re: [postgis-users] images in postgresql


Robert,
 
Is there a reason why you have the points in a separate table or do you have
points in both tables and you want to relate by a spatial join?
 
  If its a 1 to 1 relationship, we would just put them in the same table.
 
As far as foreign keys go

[postgis-users] Create Voronoi Diagrams

[ΑΠΟΣΤΟΛΟΣ ΛΕΛΕΝΤΖΗΣ]

Hello everybody

For another time, i ask your help and your experience!!
Specifically, i want to create voronoi diagrams with postgis, and i found
the following intersting link
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
So, i have installed the pl/r language and i have run the voronoi function.
But, i don't understand what kind of my data i have to place, at the below
line code
WHERE town = ''BOSTON'' AND grades LIKE ''%3%'' AND type = ''PUB''
why the writer use a WHERE statement ??
what does mean the variable "%3%" ??

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

[postgis-users] Postgis offline install on fedora.

[Smith Roman]

Hello,

What happens if my fedora set up does not have access to the internet ? Is 
there a bin file for postgis ? The postgis installer has an exe file. 
I installed postgresql 9 using the fedora bin file.

Cheers,



Message: 6
Date: Mon, 07 Mar 2011 08:31:02 +
From: Pedro Doria Meunier 
Subject: Re: [postgis-users] Postgis installation on fedora.
To: PostGIS Users Discussion 
Message-ID: <4d7497c6.10...@netmadeira.com>
Content-Type: text/plain; charset=ISO-8859-1


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

yum install postgis

HTH
Pedro.

On 03/07/2011 08:23 AM, Smith Roman wrote:
> Hello,
>
> Postgis installation on fedora.
>
> I intend to try out the installation of postgis on fedora. I have
downloaded the postgresql 9 and successfully installed it on fedora.
> To install postgis I can use the stack builder application. I will like
to ask if there is a postgis bin or rpm installer similar to the
> windows .exe installer available ? Is stack builder the only option ?
>
> Cheers,
>
> Roman.
>


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

Re: [postgis-users] Infinite loop in st_intersects - because of incorrect data out of st_transform?

[Magnus Hagander]

On Mon, Feb 28, 2011 at 16:35, Mark Cave-Ayland
 wrote:
> On 28/02/11 15:18, Magnus Hagander wrote:
>
>> Hi!
>>
>> Running the following query locks up postgis completely (in
>> geos::algorithm::RobustDeterminant):
>>
>> SELECT st_intersects(somegeometry,
>>
>> '010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry)
>>
>> I believe this is because there are infinite values in that geometry:
>>
>> # select
>> ST_AsText('010320E61001000500737979F3DDCC2CC0F92154F9E7534540F07FF07F8F806E993F7E55C0304B29FFEA8554400634E8D1DD424540B5FEE6A37FCD4540737979F3DDCC2CC0F92154F9E7534540'::geometry);
>>
>>   st_astext
>>
>> ---
>>  POLYGON((-14.4001308522972 42.6555167828373,inf inf,-85.9726317957995
>> 82.0924680617579,42.5223944076352 43.6054577711015,-14.4001308522972
>> 42.6555167828373))
>> (1 row)
>>
>>
>> ISTM that this should either be rejected as an invalid geometry, or at
>> least not hang
>
> Hi Magnus,
>
> H - I can definitely reproduce this on trunk with GEOS 3.2 series. The
> backtrace from inside GEOS looks like this:

Since nobody appears to be too interested in producing a quick fix in
geos, attached is a patch that puts in an *ugly* workaround in
PostGIS, that simply rejects the infinite values higher up in the
stack. I don't consider this a long-term fix, but it at least causes
an error instead of getting stuck in an infinite loop that can only be
terminated with kill -9... So pending a solution in geos, I would
suggest this workaround (or something better located written by
someone who actually know the postgis code better than me) be put in.
The way it is now, any application that allows the user to specify
input that could generate such a geometry could trivially
denial-of-service any postgis site...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
Index: postgis/lwgeom_geos.c
===
--- postgis/lwgeom_geos.c	(revision 6880)
+++ postgis/lwgeom_geos.c	(working copy)
@@ -2215,6 +2215,26 @@
 		{
 			PG_RETURN_BOOL(FALSE);
 		}
+
+		/*
+		 * If any of the geometries contain infinity, just error out,
+		 * because if it's passed to the intersects functions it causes
+		 * infinite loops that cannot be broken with query cancel or
+		 * even session terminate.
+		 *
+		 * XXX: mha: This should be fixed in the underlying libraries! This
+		 * is just a workaround!
+		 */
+		if (isinf(box1.xmin) || isinf(box1.xmax) ||
+			isinf(box1.ymin) || isinf(box1.ymax))
+			ereport(ERROR,
+	(errmsg("infinite value found in geometry %i, cannot run intersects()",
+			1)));
+		if (isinf(box2.xmin) || isinf(box2.xmax) ||
+			isinf(box2.ymin) || isinf(box2.ymax))
+			ereport(ERROR,
+	(errmsg("infinite value found in geometry %i, cannot run intersects()",
+			2)));
 	}
 
 	/*
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Increase query performance

[Andreas Forø Tollefsen]

Hi all,

Another question on postgis query performance. I did some discussion on the
pgsql performance list on how i could increase the performance on my server.
I did manage to increase the transactions per second, but came to the
conclusion that this did not help the ST_Intersection query which i was
trying to speed up.

Any suggestions on how to speed up this query? Basically i want to create an
intersection between a vector grid and country shapefiles. Then calculate
the area of all the polygons in the intersected table, and finally selecting
the country code for each cell which represents the largest area within each
cell. I does work as it is, but i would like to increase the speed. The
query which takes a lot of time is the ST_Intersection.

"PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"

"POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September
2009" LIBXML="2.7.7" USE_STATS"


DROP TABLE IF EXISTS cshapesgrid1946;

SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM
priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear
>=1946 AND cshapeswdate.startdate <= '1946/1/1';

ALTER TABLE cshapesgrid1946 ADD COLUMN area float;

UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);

DROP TABLE IF EXISTS pg1946;

SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT
MAX(area) FROM cshapesgrid1946 b GROUP BY divider);

CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);


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

Re: [postgis-users] Creating a grid purely in SQL

[Andreas Forø Tollefsen]

Hi Puneet,

I used Python to create a list of centroid coordinates based on the
specified size of each cell, then i used the ST_Envelope to create the walls
of each grid cell.
Like this:
UPDATE priogrid SET cell=geometry(ST_MakeEnvelope(xcoord-"+str(xhalf)+",
ycoord-"+str(yhalf)+", xcoord+"+str(xhalf)+", ycoord+"+str(yhalf)+", 4326));

2011/3/8 Paragon Corporation 

> I think uDig still has that problem last we checked, but I don't think QGIS
> does.  QGIS lets you pick the column.
>
> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mr.
> Puneet Kishor
> Sent: Monday, March 07, 2011 11:54 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Creating a grid purely in SQL
>
> Thanks Leo, for the code fragment.
>
> Re. storing multiple geometries in one column, I found the following caveat
> --
>
> [http://lists.refractions.net/pipermail/udig-users/2009-March/000553.html]
>
> Briefly, is it possible that programs such as QGIS/uDig, etc. would get
> confused about which column to draw? Does that concern still apply?
>
>
> On Mar 7, 2011, at 11:50 PM, Paragon Corporation wrote:
>
> > Puneet,
> >
> > Something like this? This is a bit old.  We have some example in our
> > book using Common Table Expressions (CTEs) in chapter 8.  You can
> > download the code to see those as well.
> > http://www.postgis.us/chapter_08
> >
> > http://www.bostongis.com/postgis_translate.snippet
> >
> > As far as storing two geometry columns in one table.  Yes -- Just do
> > it by creating another column that is geometry (or using
> > AddGeometryColumn function).  We do it all the time.
> >
> > Leo
> > http://www.postgis.us
> >
> >
> >
> > -Original Message-
> > From: postgis-users-boun...@postgis.refractions.net
> > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mr.
> > Puneet Kishor
> > Sent: Monday, March 07, 2011 11:13 PM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Creating a grid purely in SQL
> >
> > Given a top-left starting point [ulx, uly], and a cell width 'w' and
> > height 'h', is it possible to create a table entirely in SQL populated
> > with rows increasing from left to right up to X and top to bottom up
> > to Y. The table schema would be something like --
> >
> > CREATE TABLE cells (
> >  cell_id INTEGER NOT NULL,
> >  xmid DOUBLE PRECISION,
> >  ymid DOUBLE PRECISION,
> >  the_geom GEOMETRY,
> >  CONSTRAINT cells_pkey PRIMARY KEY (cell_id) );
> >
> > where xmid = (xmin + xmax) / 2 and ymid = (ymin + ymax) / 2, [xmin,
> > ymin, xmax, ymax] being the corners of each cell.
> >
> > A bonus question -- is it possible to store two geometry columns in
> > one table? For example, if I wanted to store the geometry for both the
> > center points [xmin, ymid] as well as the box [xmin, ymin, xmax,
> > ymax], would that be possible? Would that even be recommended (for
> > example, to speed up queries/drawing, etc.).
> >
> > 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] Editing a shapefile from PostGIS

[Paolo Cavallini]

Il giorno lun, 07/03/2011 alle 17.18 -0500, Dheeraj Chand ha scritto:

> This is throwing off my labeling in a major way.  Is there some way so
> sort of take an eraser, so to speak, to those small islands of 9 and
> 20 that are contained in each other?

GRASS (usable also from QGIS) can help you a lot in cleaning up
topologies.
All the best.
-- 
Paolo Cavallini: http://www.faunalia.it/pc

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

Re: [postgis-users] images in postgresql

[Robert Buckley]

Thanks for all the replies.

My Wind turbine table will only have around 300 - maximum 500 points. The 
photos 
shouldn´t change until either a turbine vanishes or gets repowered (ie. 
upgraded).

I am pretty new to postgis so when you all start talking about TOAST I start 
thinking about food rather than data formats. TOAST, Large blob, OID...how do I 
decide? As the images will be (hopefully) displayed over the web and they 
shouldn´t be more than 50 kb each. They are simply there to display a nice 
picture when someonw clicks on the map. 


The table will however be updated with new turbines and we will certainly find 
errors so that some will have to be deleted or moved.

Would it be easier to keep them all in one table rather than in separate tables?

yours,

Rob






Von: Paragon Corporation 
An: PostGIS Users Discussion 
Gesendet: Dienstag, den 8. März 2011, 6:00:00 Uhr
Betreff: Re: [postgis-users] images in postgresql

 
Ben,
 
My understanding is the same (as long as you don't select the  column that is) 
otherwise has to be detoasted. As I recall, I think a small bit  will be stored 
and then the rest that doesn't fit into (I can't recall maximum  space), gets 
chunked into toast records.
 
Its true for most of the databases I've worked with - e.g.  large text or blobs 
just the pointer is stored in the main table, except  PostgreSQL makes this 
decision conditionally on size and other databases make it  beforehand based on 
data type.
 
However -- UPDATES will be painful I think because even though  the data is 
toasted, PostgreSQL will still create an MVCC copy of the  whole record when 
doing updates and slushing around big pictures and  geometries can be painful.  
So if your other wind turbine info gets changed  more often than the photos, I 
would keep them separate.
 
 
Leo
http://www.postgis.us
 



 From: postgis-users-boun...@postgis.refractions.net  
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben  Madin
Sent: Monday, March 07, 2011 8:31 PM
To: PostGIS Users  Discussion
Subject: Re: [postgis-users] images in  postgresql

Robert, 


On 06/03/2011, at 4:28 PM, Robert Buckley wrote:

Thewindturbine table exists in EPSG:4326. I made a seperate table for the 
imagesbecause I didn´t wan´t to blow the size of the wind turbine table out 
ofproportion and jeopardize performance.
>

My understanding - and if I'm wrong I need to know(!) - is that the sort of  
data you are talking about (large geometries or blobs - for your pictures) are  
not stored in the primary table, but in associated storage space, known as 
TOAST  
tables. 

This has important implications for indexing, but is brilliant because the  
content of these data fields does not directly impact on the number of pages  
that the table takes, hence rapid searching is still possible.

cheers

Ben





>Iam making a simple application to show wind turbines as wms and I wanted 
>to
>show the turbine in a popup. I´m not sure how to get the popup to display
>though.
>
>Any examples?
>Thanks,
>
>Rob
>
>
>
>
>
>

 Von: Paragon Corporation 
>An: PostGIS Users Discussion 
>Gesendet: Samstag, den 5. März 2011, 18:21:49Uhr
>Betreff: Re: [postgis-users] images inpostgresql
>
>
>Robert,
> 
>Is there areason why you have the points in a separate table or do you 
>have 
>points inboth tables and you want to relate by a spatial join?
> 
>  If its a1 to 1 relationship, we would just put them in the sametable.
> 
>As far asforeign keys go, you should have some identifier the same in the 
>twotables.  Do you? 
> 
>So it would beof the form
> 
>SELECTwt.wt_id, wt.geom, p.picture
>FROMwindturbines As wt INNER JOIN pictures As p ON wt.wt_id =p.wt_id
> 
>or if they arespatially related by space
> 
> 
> 
>SELECT wt.wt_id, wt.geom,p.picture
>FROM windturbines As wt INNER JOIN pictures Asp ON ST_DWithin(wt.geom, 
>pt.geom, 10)
> 
> 
>The 10 dependson the spatial reference system or if you are using 
>geography 
>type then itmeans 10 meters.  So I'm treating the wind turbine location 
>and 
>picturelocation as the same if they are within 10 meters apart.
> 
>BTW: you mightwant to read the first chapter of our upcoming book.  It's a 
>freedownload and answers this type of question with concreteexamples.
>http://www.postgis.us/chapter_01
> 
>Leo
>http://www.postgis.us
> 
> 
>
>

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On
n Behalf Of RobertBuckley
>Sent: Saturday, March 05, 2011 5:39AM
>To: postgis-users@postgis.refractions.net
>Subject: [postgis-users] images inpostgresql
>
>
>Hi,
>
>Iam just experimenting at the moment with a project and could do with some 
>   
>advice.
>
>I have created a database which contains photos