Posts:
7
Registered:
11/22/02
|
|
|
|
ST_Spatial Format / Performance
Posted:
Nov 26, 2007 12:04 PM
|
|
Hello,
we are currently evaluating Spatialware on SQL Server 2005 and we have to issues that we would like to solve:
1. Is there a way to read the st_spatial directly, without converting them to WKB or WKT? We are not using MapInfo and it seems unnecessary to convert the sw_geometry twice (from Spatialware's internal format to WKB and a second time from WKB to our own internal format). This of course means, that we need to know how the ST_Spatial is organized and how we can extract the spatial data from it.
2. We have a spatial query that runs against a table with german postal codes (polygons; approx. 8300 rows; avg datalength of sw_geometry is 4814). We use ST_Overlaps to see which polygons are in a given HG_Box. The query slows down dramatically the bigger the HG_Box is. If we query with an box that is quite big, the query runs between 300 and 600 millisecs, depending on the map-sector we're in. The more polygons, the longer it takes, that's quite understandable and an evidence that the spatial index is working, I guess. The problem is: we don't need any accuracy for this query. It would be enough, if the bounding boxes (envelopes) of the polygons would be checked. Unfortunately, so far nothing we tried showed better performance. Neither using HG_Envelope nor HG_Filter in combination with the ST_Overlaps. As an unexperienced Spatialware user, I might miss some facts, hence this message. It might seem that 600ms is quite a good performance, but our original components are using bounding boxes only and the performance is at no time worse then 40 to 50ms. We fully understand that ST_Overlaps does quite a bit more work than just checking bounding boxes, but we can't justify worse performance by pointing out that the calculations are more precisely without any need.
Any information on this two topics is highly appreciated.
Thank you,
Malte Rathke
|
|
|
Posts:
135
Registered:
6/30/03
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Nov 27, 2007 5:18 PM
in response to: -
|
|
Hi Malte,
1. Within SQL Server you have the HG_GetString function that returns the ST_SPATIAL as text or the HG_AsText which returns the geometry as OGC WKT (as you already know). As far as I know, there is no other method of decoding the ST_SPATIAL BLOB within SQL Server.
2. If your have created the R-Tree indices, then you should be able to filter your objects based on the MBR. Have a look at Table-Valued UDFs in the manual, the primary filtering is possibly what you are looking for.
Hope this helps.
Cheers Ian
|
|
|
|
|
Posts:
7
Registered:
11/22/02
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Nov 28, 2007 1:55 PM
in response to: Ian Tidy
|
|
Hello Ian,
thanks for your answer.
1. Regarding the ST_SPATIAL blob: I'm currently reading the blob with the function HG_AsBinary, which returns the geometry as WKB. The binary format is far more compact. I can also read the native st_spatial image as a blobfield, but I don't know the format the binary is in, so it's needless to say that this makes no sense. I'm wondering what might be so special about the st_spatial structure, that it is still not accessible for Spatialware users. I assume that converting from an internal spatialware image to WKB is no huge process for the server, but still this could easily be avoided and performance could be used somewhere else.
2. I've read TAB files into the sql server with MapInfo's EasyLoader in the latest version. And yes, spatial indices where created. I will check the UDFs, but as far as I've understood the manual, I can't use UDFs and SP_SPATIAL_QUERY together. The latter I need because I don't have the origianl ST_SPATIAL format and I need the function HG_AsBinary which is not possible with UDFs. Besides this I could need some other spatial functions (giving me the centroid of the geometry and so on). But maybe I'm missing a point somewhere
Thanks,
Malte
|
|
|
|
|
Posts:
135
Registered:
6/30/03
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Nov 29, 2007 2:16 PM
in response to: -
|
|
Hi Malte,
1. The SP_SPATAIL blob is far more complex than what is required to create WKB/ WKT objects and only PB MapInfo can answer why the format is not released to SpatIalWare Users (but I can think of a couple of reasons).
2. In your other post you mentioned that you didn't want to create temporary tables and I think the Table Valued UDFs would require you to do this. And yes, you want to avoid using the UDFs if you can, they will generally slow down your query. Have you tried swapping the order of the geometries in the ST_OVERLAPS?
Otherwise, I am running out of ideas.
Cheers Ian
|
|
|
|
|
Posts:
7
Registered:
11/22/02
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Nov 29, 2007 2:48 PM
in response to: Ian Tidy
|
|
Hi Ian,
1. Yes, I've noticed that the ST_SPATIAL has some overhead. I can't think of reasons why the format is not released, but that may be my lack of imagination
It's mainly some points and some structure, not that complex in the end, but ok then, so it is.
2. I will try what you suggested.
Thanks again Ian for your support,
Greetings from germany,
Malte
|
|
|
|
|
Posts:
7
Registered:
11/22/02
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Nov 29, 2007 6:44 AM
in response to: -
|
|
Hello again,
it seems that nothing really helps so far. The UDFs can't be used, because I also need the geometry (HG_AsBinary) and I don't want to use a temporary table/query (first select using UDF and second one using SP_SPATIAL_QUERY).
So maybe there are other workarounds I don't know of?
Thanks again,
Malte
|
|
|
|
|
Posts:
1
Registered:
10/30/09
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Oct 30, 2009 6:19 AM
in response to: -
|
|
[nobr]SQL is crrently evaluating server i think...anyways thanks for sharing..
<table border="0" cellspacing="0" cellpadding="0" width="833" style="border-collapse: collapse; width: 625pt"> <tbody> <tr height="20" style="height: 15pt"> <td class="xl67" width="833" height="20" style="height: 15pt; width: 625pt">
Dig Bands
</td> </tr> </tbody></table>
[/nobr]
|
|
|
|
|
Posts:
3
Registered:
2/5/10
|
|
|
|
Re: ST_Spatial Format / Performance
Posted:
Feb 5, 2010 11:58 AM
in response to: saivenkat
|
|
[nobr]<table border="0" cellspacing="0" cellpadding="0" width="72" style="border-collapse: collapse; width: 54pt"> <tbody> <tr height="20" style="height: 15pt"> <td class="xl66" width="72" height="20" style="height: 15pt; width: 54pt">
hello hello hello !!!
its really nice post......
buick park avenue superchargers
</td> </tr> </tbody></table>
[/nobr]
|
|
|
|
Legend
|
|
Helpful Answer
|
|
Correct Answer
|
|
