Re: [postgis-users] may have postgis store limits for a big project?
Sebastian E. Ovide wrote: Hi Mark, what hardware would you use to manage tables of 40M rows ? (simple queries without Joins !) Each query must response in under 0.1 sec. thanks Hi Sebastian, In terms of CPU power, any modern multi-core processor will perform absolutely fine. Make sure that you have plenty of RAM for caching (more than 4Gb) with nice fast disks to boot. As long as the majority of your queries don't return more than a few hundred rows then I think you will be fine. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
On Mon, Jul 19, 2010 at 6:25 PM, Pierre Racine pierre.rac...@sbf.ulaval.cawrote: We do not know yet.. it could be even hundreds ! WKT Raster is not really limited in size. But we still have to test on datasets the size you are using. Are you aware of any production deployment ? I mean... any big deployment... If you save extra money by not having buying an Oracle license and you are interested in collaborating in the development of WKT Raster, let us know ;-) why not ? need first to convince the board that WKT can compete with Oracle... ;-) -- Sebastian E. Ovide ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
It's still on an experimental branch... but who knows, maybe someone has a big production deployment :) people are strange (like the folks who deployed PostGIS 0.1 in Solothurn, right Horst?) You might want to at least try it and see... the two days you invest in research are probably well worth it when you're looking at 6-figure deployment costs down the line to go with Oracle. Why not benchmark them both, while you're at it? Again, a week of testing to confirm a huge purchasing decision, not a big investment when placed against the risks of choosing and having to manage balky technology. P On Thu, Jul 22, 2010 at 9:28 AM, Sebastian E. Ovide sebastian.ov...@gmail.com wrote: On Mon, Jul 19, 2010 at 6:25 PM, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: We do not know yet.. it could be even hundreds ! WKT Raster is not really limited in size. But we still have to test on datasets the size you are using. Are you aware of any production deployment ? I mean... any big deployment... If you save extra money by not having buying an Oracle license and you are interested in collaborating in the development of WKT Raster, let us know ;-) why not ? need first to convince the board that WKT can compete with Oracle... ;-) -- Sebastian E. Ovide ___ 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] may have postgis store limits for a big project?
Hi Mark, what hardware would you use to manage tables of 40M rows ? (simple queries without Joins !) Each query must response in under 0.1 sec. thanks On Tue, Jul 20, 2010 at 12:04 PM, Mark Cave-Ayland mark.cave-ayl...@siriusit.co.uk wrote: salas wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky Since PostGIS makes use of the underlying PostgreSQL database, then you may find the following table useful: http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F For several projects I've been involved in, we've had databases into the 10s of millions of rows and PostgreSQL/PostGIS has handled it fine without breaking a sweat :) HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Sebastian E. Ovide ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
salas wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky Since PostGIS makes use of the underlying PostgreSQL database, then you may find the following table useful: http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F For several projects I've been involved in, we've had databases into the 10s of millions of rows and PostgreSQL/PostGIS has handled it fine without breaking a sweat :) HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
Hi All, I am interesting too... I am considering PostGIS+WKTRaster instead of Oracle GeoRaster... but for that can happen, Postgresql must be able of managing some simple tables of 30+M (Millions) rows plus a lot of Rasters of 16GB each... (16GB not compress data... so are huge images !). Each query (simple look up) must response in a fraction of second any successful case ? Thanks ! On Fri, Jul 16, 2010 at 1:13 AM, salas fsa...@geocuba.cu wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Sebastian E. Ovide ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
Hm, with rasdaman (raster data manager, www.rasdaman.org) the French National Geographic Institute maintains a dozen-TB orthoimage since about 5 years now with quite speedy access (a few 100ms), including random subsetting. Also, further on-the-fly data massaging (band extraction, recombination, classification, ...) is possible in SQL style. Currently rasdaman is getting attached to GDAL, and a PostgreSQL link for mixed queries is under development as well. -Peter On 07/19/2010 05:33 PM, Sebastian E. Ovide wrote: Hi All, I am interesting too... I am considering PostGIS+WKTRaster instead of Oracle GeoRaster... but for that can happen, Postgresql must be able of managing some simple tables of 30+M (Millions) rows plus a lot of Rasters of 16GB each... (16GB not compress data... so are huge images !). Each query (simple look up) must response in a fraction of second any successful case ? Thanks ! On Fri, Jul 16, 2010 at 1:13 AM, salas fsa...@geocuba.cu mailto:fsa...@geocuba.cu wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Sebastian E. Ovide -- Dr. Peter Baumann - Professor of Computer Science, Jacobs University Bremen www.faculty.jacobs-university.de/pbaumann mail: p.baum...@jacobs-university.de tel: +49-421-200-3178, fax: +49-421-200-493178 - Executive Director, rasdaman GmbH Bremen (HRB 147737) www.rasdaman.com, mail: baum...@rasdaman.com tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882 Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec preripiat quisquam non sibi parata. (mail disclaimer, AD 10xx) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
Hi Pierre, We do not know yet.. it could be even hundreds ! each region (european country, US state, province, etc...) would have few raster (even 20) representing different kind of information... (each pixel would be 5mx5rm)... and I need to know the value of a pixel for all the rasters in that particular coordinates... with Oracle 11g GEORaster it is possible to compress the rasters (and get lower performance of course)... in my case the compression rate is up to 600 !!!... so the amount of data is drastically reduced... nevertheless I need to be able to do a few (up to 20) queries like this at the same time in in a fraction of second...: select pixel from raster where (x=X and y=Y). One query for each raster, representing a different information If WKTRaster is mature enough, we can create a cluster without the need of paying a prohibitive license per CPU as for Oracle ! Sebastian On Mon, Jul 19, 2010 at 5:00 PM, Pierre Racine pierre.rac...@sbf.ulaval.cawrote: Hi Sebastian, What kind of query are you planning on the raster? You know you can also use WKT Raster to simply “register” filesystem rasters, creating a king of geospatial catalog, without actually storing them in the database… How many 16GB raster do you have? Are your raster all representing the same theme? Are they overlapping (or they form a regular grid)? We don’t know of that many users cases up to now, but WKT Raster prooved to be very stable working on 1 GB raster coverage (see the tutorial). Pierre *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Sebastian E. Ovide *Sent:* 19 juillet 2010 11:34 *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] may have postgis store limits for a big project? Hi All, I am interesting too... I am considering PostGIS+WKTRaster instead of Oracle GeoRaster... but for that can happen, Postgresql must be able of managing some simple tables of 30+M (Millions) rows plus a lot of Rasters of 16GB each... (16GB not compress data... so are huge images !). Each query (simple look up) must response in a fraction of second any successful case ? Thanks ! On Fri, Jul 16, 2010 at 1:13 AM, salas fsa...@geocuba.cu wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Sebastian E. Ovide ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Sebastian E. Ovide ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] may have postgis store limits for a big project?
We do not know yet.. it could be even hundreds !
WKT Raster is not really limited in size. But we still have to test on datasets
the size you are using.
each region (european country, US state, province, etc...) would have few
raster (even 20) representing different kind of information... (each pixel
would be 5mx5rm)...
With WKT Raster, you can store all those rasters as a unique tiled coverage. In
Oracle probably you stored them one table per raster?
and I need to know the value of a pixel for all the rasters in that particular
coordinates...
For this you typically use ST_Value() like this:
SELECT ST_Value(rast, ST_Geomfromtext('Point(-78.1 58.1)', 4326))
FROM srtm_tiled_100x100
WHERE ST_Intersects(rast::geometry, ST_Geomfromtext('Point(-78.1 58.1)', 4326))
One query like this takes 16 milliseconds on my machine on a 100 pixels x 100
pixels tiled coverage. There are 46800 tiles.
Don't forget to assign a reference system to your raster when converting it to
.sql (-s gdal2wktraster's option) and to construct an index on the table (-I
option).
with Oracle 11g GEORaster it is possible to compress the rasters (and get
lower performance of course)... in my case the compression rate is up to 600
!!!... so the amount of data is drastically reduced...
WKT Raster relies on PostgreSQL for compression. I don't know the compression
rate.
nevertheless I need to be able to do a few (up to 20) queries like this at the
same time in in a fraction of second...: select pixel from raster where (x=X
and y=Y). One query for each raster, representing a different information
If you tile and index everything properly that should not be a big deal. Let us
know!
If WKTRaster is mature enough, we can create a cluster without the need of
paying a prohibitive license per CPU as for Oracle !
If you save extra money by not having buying an Oracle license and you are
interested in collaborating in the development of WKT Raster, let us know ;-)
Pierre
Sebastian
On Mon, Jul 19, 2010 at 5:00 PM, Pierre Racine pierre.rac...@sbf.ulaval.ca
wrote:
Hi Sebastian,
What kind of query are you planning on the raster? You know you can also use
WKT Raster to simply register filesystem rasters, creating a king of
geospatial catalog, without actually storing them in the database...
How many 16GB raster do you have? Are your raster all representing the same
theme? Are they overlapping (or they form a regular grid)?
We don't know of that many users cases up to now, but WKT Raster prooved to be
very stable working on 1 GB raster coverage (see the tutorial).
Pierre
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Sebastian
E. Ovide
Sent: 19 juillet 2010 11:34
To: PostGIS Users Discussion
Subject: Re: [postgis-users] may have postgis store limits for a big project?
Hi All,
I am interesting too... I am considering PostGIS+WKTRaster instead of Oracle
GeoRaster... but for that can happen, Postgresql must be able of managing some
simple tables of 30+M (Millions) rows plus a lot of Rasters of 16GB each...
(16GB not compress data... so are huge images !). Each query (simple look up)
must response in a fraction of second
any successful case ?
Thanks !
On Fri, Jul 16, 2010 at 1:13 AM, salas fsa...@geocuba.cu wrote:
Hello to all:
I am working in a project where they are managed more than 20 geoespatials
thematics. Each thematic one has a considerable volume of since information it
is of the whole country (I am speaking of a lot more than a million of
records). In the project we need to make (mostly) intersection consultations
keeping in mind literal attributes.
I need to know the experience of somebody in a project of this span and if
PostGIS would present some limitation therewith.
regards yuriesky
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Sebastian E. Ovide
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Sebastian E. Ovide
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
