SharpMap PostGIS Slow

Aug 30, 2008 at 3:39 AM
I'm not sure if this should be here or in a postgresql forum but I am experiencing very slow performance with retrieving data from postgresql.  I am just getting started with SharpMap and I am using SharpMap v0.9, PostgreSQL 8.3, and PostGIS 1.3.3. The bottleneck is the database for some reason.  I have a table of streets with 22 columns and around 80,000 rows and the following sql being executed that takes 12+ seconds:

SELECT AsBinary(the_geom) AS Geom FROM mainroads2 WHERE the_geom && box2d('BOX3D(-99.0017 29.0837142857143,-98.0017 29.7622857142857)'::box3d)

I've created a gist index and selecting any column other than "the_geom" is fast.  Any suggestions would be greatly appreciated.
Sep 9, 2008 at 8:42 PM
hi

I have same problem with oracle, after some testing I review the problem is the time of transformation from geometry to wkb or wkt standard, I resolve (partialy) the problem modify the conector (oracle.cs) to use directly wkb and wkt, this new field are preview stored (like update poligons set wkb = asbinary(the_geom)), plus add a limit of record view because sharpmap render all poligons (include small poligons than can't view because scale), aditional (because use a second field not original geometry) can simplify the geometry using the Douglas Pecker algoritm (review the funcion in postgis). I need now limit the render if is not visible  to determinated scale but I work on this.

Try to modify the conector.

regards.
Sep 26, 2008 at 12:14 PM
Hi
I have same problem, How you create new field binary?? the type column on Oracle.
and how you limit teh records? What you used?

Sorry my English

regards
Sep 29, 2008 at 5:08 PM
in oracle you can use function on oracle locator (not Express) http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/sdo_util.htm specific use the function: 

update

 

 

 tablewithsdogeometryfield set fieldwkb = SDO_UTIL.TO_WKBGEOMETRY(geometry)

to simplify you can use SDO_UTIL.SIMPLIFY to simplify this field (if you need).

I work now with a class to exclude geometrys very small but have some problems (and less time) to make this. if you need the oracle.cs modify class send me a email because unknow how add to this site the library.

regards.

 

Coordinator
Sep 30, 2008 at 10:03 AM
Hi Ojarana, you can submit a patch via the source code >> patches page hth jd
Oct 29, 2008 at 1:11 PM

Hi Ojarana

I created tablewithsdogeometryfield set fieldwkb = SDO_UTIL.TO_WKBGEOMETRY(geometry)

Now as you assign the fieldwkb value to "geometry" sharpmap




ojarana

29/09/2008 18:08

Por favor, responda a
SharpMap@discussions.codeplex.com

Para
wuilmer_penaloza@ieci.es
cc
Asunto
Re: SharpMap PostGIS Slow [SharpMap:34589]





From: ojarana

in oracle you can use function on oracle locator (not Express) http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/sdo_util.htm specific use the function:

update

tablewithsdogeometryfield set fieldwkb = SDO_UTIL.TO_WKBGEOMETRY(geometry)

to simplify you can use SDO_UTIL.SIMPLIFY to simplify this field (if you need).

I work now with a class to exclude geometrys very small but have some problems (and less time) to make this. if you need the oracle.cs modify class send me a email because unknow how add to this site the library.

regards.

Read the full discussion online.

To add a post to this discussion, reply to this email (SharpMap@discussions.codeplex.com)

To start a new discussion for this project, email SharpMap@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Este mensaje, y en su caso, cualquier fichero anexo al mismo, puede
contener informacion confidencial, siendo para uso exclusivo del 
destinatario, quedando prohibida su divulgacion copia o  distribucion a 
terceros sin la autorizacion expresa del remitente. Si Vd. ha recibido 
este mensaje erroneamente, se ruega lo  notifique al remitente y 
proceda a su borrado.
Gracias por su colaboracion.

This message (including any attachments) may contain confidential 
information. It is intended for use by the recipient only. Any 
dissemination, copying or distribution to third parties without the 
express consent of the sender is strictly prohibited. If you have 
received this message in error, please delete it immediately and 
notify the sender. 
Thank you for your collaboration.