[GRASSLIST:918] Re: Handling millions of points in GRASS...

[Radim Blazek]

On Wednesday 06 August 2003 18:23, David Finlayson wrote:
> I have a bathymetry project that is attempting to collect and map every
> sounding ever collected for Puget Sound, Washington. So far, I have
> several million soundings stored in a MySQL database. But with the new
> Sonar aboard our vessel, the new interest in bathymetric LIDAR and
> cooperation with NOAA, that number is expected to grow exponentially in
> the next few years.
>
> What is the best way to get this data into GRASS?  What's the best way
> to manage it?
>
> I know that RDBMS support is coming soon and that (apparently) PostGIS
> is already functional on some platforms (Linux, Cygwin?).  But what is
> the word from the trenches?  I have been very happy with MySQL and would
> rather use this if functionality is comparable to PostGres, but it would
> be better to move now rather than wait if necessary...
>
> A major task that needs to be handled is some way to select points both
> by spatial location (polygons not boxes) and by attributes such as age,
> survey, and platform.  Currently, I have a table of soundings (x,y,z,ID)
> and a table of headers  (ID,date,platform,etc.) and I load it into
> ArcGIS (ESRI, inc.) through ODBC where I can grid up the data into
> rasters for further analysis. Hence, I am currently dependent on Windows.

MySQL is not currently supported by many GIS applications. PostGIS/PostgreSQL
is supported for example by Mapserver, OGR, QGIS and GRASS.

In GRASS 5.1 is possible to use directly data from PostGIS, i.e. without conversion.
BUT, there are some limitations and at present, it is not very effective. 
It works but it is slow. More work and testing has to be done on that part of library.

It is possible to create a new vector as a definition of PostGIS table(s) where 
geometry (points) and attributes are stored. Such vector may be used as any 
other vector in GRASS, except editing. So you can for example display, query, 
extract or run v.surf.rst on that. 

Problems:
- missing optional SQL condition in the definition of PostGIS vector in GRASS
  (of course, it is possible to create a view in Posgres and use it in GRASS,
   but such view is created for each query and that is very slow)
- vector contains the data specified when it was created, most of other modules
  do not support SQL query, that means to run v.surf.rst on different points
  you have to create new vector or use v.extract before.
- performance

So, we are waiting for somebody like you, who needs to use it and who will improve
the functionality :)

To be frank, if you need to work with large data sets, forget about RDBMS and use 
text files :). Last month I tried to make a query on 30000 rows in Postgres,
I had to kill that after 3 hours. OK, maybe my query was not optimized,
but perl script reading from text file did the job in 6 minutes!
If you want RDBMS + GRASS, then 
psql | s.in.ascii + s.* 
is probably the best you can do.

Radim