| 03:40:16 | low: | hi there. is it possible to render a circularstring with mapserver ? we can't get it to work |
| 03:48:55 | low: | looks like it won't work...unknown geometry type |
| 03:52:13 | low: | what would be the best way to store that circle into postgis ? http://www.eumetsat.int/Home/Main/Access_to_Data/Meteosat_Image_Services/SP_1123237865326?l=en |
| 03:55:14 | low: | oh well, i guess that'll be good enough with a multipolygon |
| 07:49:43 | lsoto: | hi everybody! |
| 07:50:11 | lsoto: | is it ok to ask usage questions here? |
| 07:50:21 | lsoto: | or is the mailing list preferred? |
| 07:50:30 | nhv: | ask away |
| 07:50:50 | nhv: | if you don't get an answer then try the list |
| 07:53:58 | lsoto: | ok |
| 07:54:02 | lsoto: | here is my problem |
| 07:54:21 | lsoto: | we are storing mixed geometries on a pair of columns on two tables |
| 07:54:41 | lsoto: | they are not of different kind; also different SRIDs |
| 07:55:06 | lsoto: | errr- they are not *only* of different kind; also different SRIDs - that is |
| 07:55:53 | lsoto: | until now, to avoid mixing SRIDs on spatial queries, we always put a "srid( foo ) = srid( bar )" guard on WHERE clauses |
| 07:56:34 | lsoto: | but we postgresql 8.3 and postgis 1.3.3 this is not working |
| 07:57:23 | lsoto: | when looking at the query plan looks like the condition execution order is changed |
| 07:58:10 | lsoto: | so we write "WHERE srid( foo ) = srid( bar ) AND intersects( foo, bar )", but the intersects is executed first and the srid comparision is executed later |
| 07:58:55 | lsoto: | or, best said, not executed at all, because the intersects call fails with the "mixed srids" problem |
| 08:07:11 | lsoto: | does anyone have an idea on what should we do? |
| 08:08:22 | mcayland: | lsoto: don't mix geometries with different srids in the same column |
| 08:09:43 | lsoto: | mcayland: we are working with UTM coordinates, and the data spans more than one UTM zone |
| 08:10:23 | lsoto: | mcayland: so we didn't saw how to avoid mixing SRID |
| 08:12:01 | pramsey: | transform your data to something that covers your whole work area |
| 08:12:15 | lsoto: | such as lat/long ? |
| 08:12:21 | pramsey: | not ideally |
| 08:12:31 | pramsey: | how big is your work area? |
| 08:12:32 | lsoto: | it seems a little overkill |
| 08:16:07 | lsoto: | pramsey: around 500 km^2 |
| 08:17:30 | pramsey: | and you're in two UTM zones? |
| 08:17:48 | lsoto: | yep |
| 08:17:54 | pramsey: | just pick one |
| 08:18:10 | pramsey: | the distortion on the shapes outside the zone will be pretty minimal |
| 08:18:21 | lsoto: | but won't we loose precision for points outside the picked UTM zone? |
| 08:18:26 | pramsey: | or find an alternate local projection. where is this, geogrpahically? |
| 08:18:33 | lsoto: | chile, south america |
| 08:18:39 | pramsey: | you'll find that distance calculations are a little off, but not much |
| 08:19:06 | pramsey: | you can also build your own tmerc projection, centered on your work area |
| 08:19:20 | lsoto: | pramsey: can you point me to material where I can find how much distortion we would get? |
| 08:19:22 | pramsey: | but it's better ( I think ) to find a local system commonly used than to invent your own |
| 08:19:32 | pramsey: | sure, use PostGIS |
| 08:19:46 | lsoto: | pramsey: ) |
| 08:19:55 | pramsey: | put your data into one zone, then compare length_spheroid( transform( ,4326 ) ) to length( ) |
| 08:20:08 | pramsey: | the first is the closes to "true" that you can get, the second is your planar approximation |
| 08:20:16 | lsoto: | cool |
| 08:20:35 | pramsey: | you'll see that even inside the zone, you get deviation |
| 08:20:49 | lsoto: | yeah, that's logical |
| 08:22:47 | lsoto: | crap, I obviously missed on the work area number |
| 08:22:59 | lsoto: | its 500.000 km^2 :$ |
| 08:23:38 | pramsey: | stick a custom tmerc down the middle |
| 08:23:45 | lsoto: | is it still a good idea to use one UTM zone? |
| 08:23:49 | lsoto: | ok |
| 08:23:54 | pramsey: | that's what Alberta does ( and Idaho ) |
| 08:24:16 | pramsey: | ( and I imagine chile does too ) |
| 08:24:22 | pramsey: | but I can't find a google reference |
| 08:24:28 | pramsey: | surely there's a preferred national projection |
| 08:24:39 | lsoto: | great |
| 08:24:43 | lsoto: | I'm going to check that |
| 08:25:14 | lsoto: | so, the "same srid rule" is a golden one : ) |
| 08:25:34 | lsoto: | happy to learn that |
| 08:26:14 | pramsey: | the only time I've used mixed srids is in staging towards a uniform srid |
| 08:28:19 | lsoto: | apart from the "mixed SRID" problem |
| 08:28:34 | lsoto: | is there other loses from doing what our team is doing now? |
| 08:28:53 | : | * lsoto now has to sell the "let's unify the SRID" idea : ) |
| 08:29:44 | pramsey: | not being able to effectively index, to do any comparisons between data in different zones, does it get any worse? : ) |
| 08:30:10 | pramsey: | I don't think any spatial database will let you get away with it. |
| 08:32:31 | lsoto: | pramsey: sounds reasonable |
| 08:32:50 | lsoto: | it's just the this project has a few years ( I'm new in the team ) and it has worked "fine" until now |
| 08:33:09 | lsoto: | but that's a political problem, and its my problem |
| 08:33:22 | lsoto: | pramsey: thanks you very much for your help |
| 08:34:10 | pramsey: | if you just want an index, there is a "way" |
| 08:34:18 | pramsey: | ( lots of other things will still be broken ) |
| 08:34:34 | pramsey: | build a functional index on transform( geocolumn, 4326 ) |
| 08:34:57 | pramsey: | then change all your query calls to use that functional index. you'll still need to have all your client code aware of your hack though |
| 08:35:21 | pramsey: | how did they use a mixed srid table effectively anyways? |
| 08:35:28 | pramsey: | never make a spatial query? only attribute queries? |
| 08:37:18 | lsoto: | also spatial queries |
| 08:37:26 | lsoto: | no indexes, I'm afraid |
| 08:37:53 | lsoto: | what about managing two columns? |
| 08:38:03 | lsoto: | one as it is now, and the other in lat/long |
| 08:38:25 | pramsey: | no real advantage that I can see |
| 08:38:36 | lsoto: | against what we have? |
| 08:38:40 | pramsey: | every once in a while, when you query over the dividing line, you'll get a set of mixed geometries |
| 08:38:48 | pramsey: | and what happens then? |
| 08:38:52 | pramsey: | how does it work now? |
| 08:39:11 | pramsey: | with the mixed srids, you have geometries that have the same coordinates in UTM, but are spatially in different places on the world |
| 08:39:23 | pramsey: | how are they working around it? |
| 08:39:47 | lsoto: | I mean, using the lat/long column for indexes and spatial queries, but the associated UTM coords would be precalculated on the same record |
| 08:40:28 | lsoto: | by now we have no real-world cases when the results of a query spans over multiple utm zones |
| 08:40:49 | lsoto: | but it is theorically possible, and not managed in any way :( |
| 08:42:06 | pramsey: | you can store the data in lat/lon ( or your general tmerc ) and use transform( ) on output to get into whatever zone you want |
| 08:42:34 | lsoto: | I see |
| 08:42:44 | pramsey: | and then all your outputs will be in teh same zone. if you keep the mixed column, you can query over the boundary and get a mixed result set... and then what do you do? |
| 08:42:59 | pramsey: | sure you have real-world results over two zones... |
| 08:43:15 | pramsey: | a feature at the center of zone 9 will have the same coordinates as a features at the center of zone 110 |
| 08:43:16 | lsoto: | well, geometry values contains SRID information AFAIK |
| 08:43:18 | pramsey: | zone 10 |
| 08:43:28 | lsoto: | so you could check for that eventually |
| 08:43:40 | lsoto: | nevertheless, I agree that it is not good |
| 08:43:56 | pramsey: | you have data with some complexity, the question is where you force out that complexity |
| 08:44:17 | pramsey: | I would argue for forcing it out very early, because it makes all the rest of the code and queries so much simpler |
| 08:44:57 | pramsey: | so my option ( a ) would be store the data in an appropriate tmerc that covers your whole area of interest |
| 08:45:01 | lsoto: | well, by now the rest of the code is very simple, but arguably wrong |
| 08:45:39 | pramsey: | if they are only using the left half of one zone and the right half of anohter, they could be getting luck with respect to coordinate overlaps |
| 08:45:55 | pramsey: | so they have a crappy design but it hasn't bit them yet |
| 08:46:04 | lsoto: | maybe |
| 08:46:06 | pramsey: | or they just aren't noticing when it is : ) |
| 08:46:47 | lsoto: | in practice, "individual working zones" ( where the final user sees ) are very small zones |
| 08:47:16 | lsoto: | it's all the dataset which spans over ~500.000 km^2 |
| 08:47:48 | pramsey: | hm |
| 08:47:50 | pramsey: | do they ever plan to cross the boundaries with queries? |
| 08:47:56 | pramsey: | another ugly ugly hack... |
| 08:47:57 | lsoto: | so, I must accept that the probability of zone overlap are quite low |
| 08:48:09 | lsoto: | nop, not planed |
| 08:49:00 | lsoto: | in practice, all queries are over such small zones that UTM-zone overlap chances are minimal, and if that happens, the cases would be promptly identified |
| 08:49:33 | lsoto: | because a client would sign a contract for an area wich is intesected by the UTM-zone boundary |
| 08:49:59 | lsoto: | and I think that the plan then would be just pick one of the two UTM zones |
| 08:50:20 | lsoto: | so the current desing is not very sound, but seems to work |
| 08:51:28 | pramsey: | build a functional index on transform( the_geom, 4326 ) |
| 08:51:49 | pramsey: | and change your query to 'where st_intersects( transform( the_geom, 4326 ), transform( querygeom, 4326 ) )' |
| 08:52:07 | lsoto: | pramsey: ok |
| 08:52:43 | pramsey: | should work, but keep your srid = srid test in place |
| 08:52:45 | lsoto: | so you think that the case for uniforming SRIDs will not be too strong, given the circunstances |
| 08:52:49 | pramsey: | so that the final output is uniform |
| 08:53:04 | pramsey: | or, wrap the output in a transform to put the outputs all in the srid of the querygeom |
| 08:53:21 | pramsey: | I think you can get away with a minimalist stopgap solution |
| 08:53:32 | lsoto: | ok |
| 08:53:34 | pramsey: | the case for going uniform is strong if you want to do other useful things with the data |
| 08:53:37 | pramsey: | like map it : ) |
| 08:53:45 | pramsey: | or do a query over the whole data set |
| 08:53:53 | lsoto: | you mean mapping the whole dataset, right? |
| 08:53:55 | pramsey: | yes |
| 08:54:10 | lsoto: | ok |
| 08:55:12 | lsoto: | what about performance? |
| 08:55:39 | lsoto: | I suppose we lose a bit with all the transform( ) gymnastic |
| 09:26:55 | lsoto: | pramsey_: thanks again for your help, it was a quite instructive chat : ) |
| 09:39:44 | pramsey: | No, not much performance loss. |
| 09:39:53 | pramsey: | the functional index takes care of that, you aren't doing many transforms |
| 10:23:35 | sigq: | geosfeed: geos_unit.rar attached to ticket #188 <http://trac.osgeo.org/geos/attachment/ticket/188/geos_unit.rar> |