[postgis-users] postgis-users Digest, Vol 84, Issue 26
WANGRUNGVICHAISRI, SHIVESH
sbw at appsig.com
Thu Aug 27 12:56:23 PDT 2009
Hi,
Here's the result returned from
SELECT postgis_full_version();
"POSTGIS="1.3.6" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS"
I basically did a PostGIS install from the PgSql's Application Stack
Builder.
Regarding the indexing...the test program that I submitted as part of
this bug report actually is an extract of our real program. In our real
program, whether or not we have index in there, the leak is present.
Thanks,
S.
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
postgis-users-request at postgis.refractions.net
Sent: Thursday, August 27, 2009 12:00 PM
To: postgis-users at postgis.refractions.net
Subject: postgis-users Digest, Vol 84, Issue 26
Send postgis-users mailing list submissions to
postgis-users at postgis.refractions.net
To subscribe or unsubscribe via the World Wide Web, visit
http://postgis.refractions.net/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-users-request at postgis.refractions.net
You can reach the person managing the list at
postgis-users-owner at postgis.refractions.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of postgis-users digest..."
Today's Topics:
1. DB and file organization best practices (dassouki)
2. Re: DB and file organization best practices (Pavel Iacovlev)
3. Re: SUM() for lines (Paragon Corporation)
4. PostGIS memory leak bug (WANGRUNGVICHAISRI, SHIVESH)
5. Re: PostGIS memory leak bug (Paragon Corporation)
6. Re: PostGIS memory leak bug (Kevin Neufeld)
7. postgis slow performance (Narayanan, Divya)
----------------------------------------------------------------------
Message: 1
Date: Wed, 26 Aug 2009 16:20:58 -0300
From: dassouki <dassouki at gmail.com>
Subject: [postgis-users] DB and file organization best practices
To: postgis-users at postgis.refractions.net
Message-ID: <4A958B1A.7010200 at gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Hey all,
I'm in the process of moving all my data to a new server. I was
wondering:
* How do you organize your GIS projects?
o DB Structure:
+ Right now, what I do is I have a main GIS db
containing GIS layers and another db containing data
that I can join to the geometry. I find that this
structure uses less storage space, but not as
organized to one DB per project.
o Folder Structure:
+ I usually follow this structure:
/gis/projects/project_ID_D_M_Y .. with sub folders:
documents, input data, used queries (Sql files),
output data, emails, research, final output, and legal
+ Is there a management solution (software or technique)
that you would recommend?
o Backups
+ What do you recommend to backing up PostGIS data? How
often do you do it?
+ I back up every project on a cd with all the data
exported as shp sql files, any better suggestions?
o General Organization
+ Any other tips, not necessarily PostGIS related, that
make your GIS life easier?
# I found that two 20 inch monitors really helped
as well as a new stick of ram.
I look forward to reading your responses,
Dasouki
------------------------------
Message: 2
Date: Wed, 26 Aug 2009 22:41:33 +0300
From: Pavel Iacovlev <iacovlev.pavel at gmail.com>
Subject: Re: [postgis-users] DB and file organization best practices
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID:
<2a37f67a0908261241j20e7c3a9mbe55bad2e2928522 at mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
This really varies from project to project, and depends on many
things, but since I am bored at the moment here is what I use:
DB Structure:
I use 1 db per project with multiple schemas
Management solutions:
SVN, Issue tracker, Wiki
Backups:
cron & pg_dumpall
General Organization:
If you are a developer and do code use management solutions in your
team, and also docs (generate project documentation from them), unit
testing, coding style & standards across all projects
On Wed, Aug 26, 2009 at 10:20 PM, dassouki<dassouki at gmail.com> wrote:
> Hey all,
>
> I'm in the process of moving all my data to a new server. I was
wondering:
>
> ? * How do you organize your GIS projects?
> ? ? ? ? o DB Structure:
> ? ? ? ? ? ? ? + Right now, what I do is I have a main GIS db
> ? ? ? ? ? ? ? ? containing GIS layers and another db containing data
> ? ? ? ? ? ? ? ? that I can join to the geometry. I find that this
> ? ? ? ? ? ? ? ? structure uses less storage space, but not as
> ? ? ? ? ? ? ? ? organized to one DB per project.
> ? ? ? ? o Folder Structure:
> ? ? ? ? ? ? ? + I usually follow this structure:
> ? ? ? ? ? ? ? ? /gis/projects/project_ID_D_M_Y .. with sub folders:
> ? ? ? ? ? ? ? ? documents, input data, used queries (Sql files),
> ? ? ? ? ? ? ? ? output data, emails, research, final output, and legal
> ? ? ? ? ? ? ? + Is there a management solution (software or technique)
> ? ? ? ? ? ? ? ? that you would recommend?
> ? ? ? ? o Backups
> ? ? ? ? ? ? ? + What do you recommend to backing up PostGIS data? How
> ? ? ? ? ? ? ? ? often do you do it?
> ? ? ? ? ? ? ? + I back up every project on a cd with all the data
> ? ? ? ? ? ? ? ? exported as shp sql files, any better suggestions?
> ? ? ? ? o General Organization
> ? ? ? ? ? ? ? + Any other tips, not necessarily PostGIS related, that
> ? ? ? ? ? ? ? ? make your GIS life easier?
> ? ? ? ? ? ? ? ? ? ? # I found that two 20 inch monitors really helped
> ? ? ? ? ? ? ? ? ? ? ? as well as a new stick of ram.
>
> I look forward to reading your responses,
> Dasouki
> ?_______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
http://iap.md, The future is open
------------------------------
Message: 3
Date: Wed, 26 Aug 2009 18:38:57 -0400
From: "Paragon Corporation" <lr at pcorp.us>
Subject: Re: [postgis-users] SUM() for lines
To: "'PostGIS Users Discussion'"
<postgis-users at postgis.refractions.net>
Message-ID: <951C7E00355544528CCF9C6424F43BAD at b>
Content-Type: text/plain; charset="us-ascii"
Are you trying to do a rolling sum where each rows sum is the sum of
previous rows in that group. Its much easier to do in PostgreSQL 8.4
The below article might help
http://www.postgresonline.com/journal/index.php?/archives/119-Running-to
tals
-and-sums-using-PostgreSQL-8.4-Windowing-functions.html
Leo
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Daniel
Grum
Sent: Wednesday, August 26, 2009 12:20 PM
To: PostGIS Mailing-List
Subject: [postgis-users] SUM() for lines
Hi all,
how can I sum the atributes of any lines of one column --> how I can sum
the
info of a column.
I want to save all avaible resources(all polygons) in an extra column
next
to the resources of one polygon?!
like this:
gid(integer) | flaeche(double precision) | sum_flaeche(double precision)
1
2000 2000
2
5000 7000
3
100 7100
And how I can select the last info of the column: sum_flaeche to wok
with
the number-->in this case: 7100
If this could not work please describe me another possible way?!
--daniel
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
Message: 4
Date: Wed, 26 Aug 2009 16:30:24 -0700
From: "WANGRUNGVICHAISRI, SHIVESH" <sbw at appsig.com>
Subject: [postgis-users] PostGIS memory leak bug
To: <postgis-users at postgis.refractions.net>
Message-ID:
<9D9EF0A21FC3914A9248DAE900EEF5FBFEC598 at EXCHANGEVS01.appsig.com>
Content-Type: text/plain; charset="us-ascii"
We have an example program that should demonstrate a memory leak in
postgres.exe caused by PostGIS when used in a multi-threaded situation.
The link to the entire zip file is here:
http://www.2shared.com/file/7399978/56887b86/Postgis_Leak.html
OS: Windows XP 32-bit
PostgreSQL: 8.3.7
PostGIS: 1.3.6
Thanks,
S.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://postgis.refractions.net/pipermail/postgis-users/attachments/2009
0826/266ce6c9/attachment-0001.html>
------------------------------
Message: 5
Date: Wed, 26 Aug 2009 23:50:18 -0400
From: "Paragon Corporation" <lr at pcorp.us>
Subject: Re: [postgis-users] PostGIS memory leak bug
To: "'PostGIS Users Discussion'"
<postgis-users at postgis.refractions.net>
Message-ID: <82F2B25529A8455EB1AC1BA8B5E15C1F at H>
Content-Type: text/plain; charset="us-ascii"
Which version of GEOS are you running
run a
SELECT postgis_full_version();
Most of issues with memory leaks in the past have happened in the GEOS
layer
as I recall.
Hope that helps,
Regina
_____
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
WANGRUNGVICHAISRI, SHIVESH
Sent: Wednesday, August 26, 2009 7:30 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] PostGIS memory leak bug
We have an example program that should demonstrate a memory leak in
postgres.exe caused by PostGIS when used in a multi-threaded situation.
The link to the entire zip file is here:
http://www.2shared.com/file/7399978/56887b86/Postgis_Leak.html
OS: Windows XP 32-bit
PostgreSQL: 8.3.7
PostGIS: 1.3.6
Thanks,
S.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://postgis.refractions.net/pipermail/postgis-users/attachments/2009
0826/a3b2e6bb/attachment-0001.html>
------------------------------
Message: 6
Date: Thu, 27 Aug 2009 00:00:40 -0700
From: Kevin Neufeld <kneufeld at refractions.net>
Subject: Re: [postgis-users] PostGIS memory leak bug
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <4A962F18.8080102 at refractions.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Just thinking this through out loud here ... as I understand it, your
test program runs two threads at the same time.
Thread 1 inserts into a table with a simple geometry object.
Thread 2 selects from the same table at the same time the inserts are
happening.
I'm not sure, but I think PGConn from libpq defaults to having
autocommit set to true. Nevertheless, you might not be experiencing a
memory leak at all, but rather growing transaction blocks within
postgresql.
Since you don't add a spatial index to your test table, it naturally
takes longer and longer to query the table using ST_Contains in your
select thread since the query would be performing sequential scans
through the entire table every time. So, as you perform a long running
query against a table in one transaction block, you are inserting into
the same table as fast as you can in a different transaction block ...
and at the same time autovacuum kicks in trying to ANALYZE your growing
table in a futile attempt to kept the stats up to date.
You probably see the memory allocations grow faster than normal due to
the larger footprint of a geometry object. I'm curious, do you see the
memory climb using other data types?
Having said that, Regina is right that there were a few nasty leaks in
GEOS a while back. Make sure you are using the latest stable build.
-- Kevin
WANGRUNGVICHAISRI, SHIVESH wrote:
>
> We have an example program that should demonstrate a memory leak in
> postgres.exe caused by PostGIS when used in a multi-threaded
situation.
>
>
>
> The link to the entire zip file is here:
>
>
>
> http://www.2shared.com/file/7399978/56887b86/Postgis_Leak.html
>
>
>
> OS: Windows XP 32-bit
>
> PostgreSQL: 8.3.7
>
> PostGIS: 1.3.6
>
>
>
> Thanks,
>
>
>
> S.
>
>
>
>
------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 7
Date: Thu, 27 Aug 2009 10:35:04 -0700
From: "Narayanan, Divya" <dnarayan at randmcnally.com>
Subject: [postgis-users] postgis slow performance
To: <postgis-users at postgis.refractions.net>
Message-ID:
<54D92E8EB0F07A4CA1FB15FDC53EC1E902B06297 at ir-ex-01.corp.randmcnally.com>
Content-Type: text/plain; charset="us-ascii"
Hi,
We were testing some queries to improve performance and found that the
queries using PostGIS were slower than those that did not.
I've included one of the queries tested
Query with PostGIS
SELECT ntb.id
FROM navteq as ntb
WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)
AND (latitude>34.00846 AND latitude<34.09855 AND longitude>-118.29926
AND longitude<-118.1907)
LIMIT 250
Query time: 1 second
Query using postgis:
SELECT ntb.id
FROM navteq_xtd_noothers as ntb
WHERE ( ntb.positioned_tsv @@ '(hotel|hodels|hoteles)'::tsquery)
AND geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-118.29926,
34.00846),ST_Point(-118.1907, 34.09855)),4269)
LIMIT 250
Query time: 4 seconds
The latitude and longitude columns are indexed. The geom column uses a
GIST index. We're using Postgres v 8.1. Is there something we're doing
wrong? Please let me know if you need more information.
Thanks,
Divya
--------------------------------------------------------
This E-mail is confidential. It should not be read, copied, disclosed or
used by any person other than the intended recipient. Unauthorized use,
disclosure or copying by whatever medium is strictly prohibited and may
be unlawful. If you have received this E-mail in error, please contact
the sender immediately and delete the E-mail from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://postgis.refractions.net/pipermail/postgis-users/attachments/2009
0827/e73bd5a2/attachment-0001.html>
------------------------------
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
End of postgis-users Digest, Vol 84, Issue 26
*********************************************
More information about the postgis-users
mailing list