It was noticed that in 4700 if user uses WMS via MapBuilder, vector data is slightly shifted. The reason for this was that WMS uses SRID 27700 when other data 81989. Solution is to reproject vector data to 27700. Please see conversation bellow. Scripts and conversations also attached to this wiki.
SR 7000577169
Scripts:
communities.bentley.com/.../chg_5F00_srid_5F00_to_5F00_27700.sql
communities.bentley.com/.../upd_5F00_srid_5F00_sdo_5F00_metadata.sql
-------------------------
Letter from Jonny (ROB's email is in the end):
Hi Gary,
Apologies I meant to send this a couple of days ago but got side tracked . I am happy to help assuming I have my time agreed on this by the HE project managers.
I did ask Rob C about this and his reply is attached. He thought that the fix (8) wouldn’t affect this.
Obviously with the WMS themes now stored in Mapbuilder and utilising the 27700 SRID I think that the data is not reprojected in the same way as when dealing with the old system where the WMS was essentially reprojected upon being consumed in Locator.
We will need to run this for other MaS customers moving to 4700 but I don’t think anyone else comes close to VM in terms of data volumes so the time to run these should be a lot quicker.
Don’t hesitate to contact me to discuss further.
Jonny
____________________
Correspondence
25.01.2017 10:22:42 KJARMALA
Gary:
…annnnnd the second script is done as well. I’ve tested it and have notified the customer. Please don’t close the call.
Best regards,
Gary
____________________
Correspondence
25.01.2017 10:22:15 KJARMALA
Letter from Gary:
Quick update – we’re now onto the second script. Projections seemed quite accurate in the end!
BTW going forward someone really really needs to understand this issue. We’re looking at doubling the amount of time it takes to upgrade a customer to 4.7.0.0, so maybe people can put their heads together and come up with a better way of doing this.
Best regards,
Gary
____________________
Correspondence
25.01.2017 10:21:53 KJARMALA
Letter from Gary:
Quick update – we were running the same scripts on another TWC 4700 system about 30 minutes or so ahead of TWC (different server, no contention). On that database the first script finished at 09:18 and the second script took about 35 minutes to finish. Maps in that system are now correct so at least this fix is looking to be good (which was not 100% certain). I’m hoping that since TWC was only 30 minutes behind this other system that TWC won’t be far off now.
Best regards,
Gary
____________________
Correspondence
25.01.2017 10:21:28 KJARMALA
Letter from Gary:
Hi Jonny,
Rik and I were just wondering if it was the move of the WMS into themes that causes this, as we do apply fix 8 and we have a post 4.7.0.0 upgrade task to create the WMS and map entry in mapbuilder and then add it to the GIS WMS Themes. The WMS data hasn’t changed and the WMS server config hasn’t changed, so that leaves fix 8.
Best regards,
Gary
____________________
Correspondence
25.01.2017 10:21:01 KJARMALA
Letter from Jonny:
Hi Gary,
The first script is the one that will take time as it is changing every spatial features srid and there’s a lot of them in TWC. The second script just changes the srid in the SDO metadata for those layers affected so should be quick to run.
I will have to ask Rob C why the move to 4.7.0.0 results in this behaviour. The 81989 SRID is slightly different from 27700 which is the one that we should be using and mixing up the SRIDs can result in the offsetting of data. But I was mistakenly under the impression that if all the data was using the same SRID then the data should be OK.
Does the move to 4.7.0.0 also mean that NM 4700 fix 8 (multiple WMS) is applied? If so, I think this is where the issue may arise as the handling of WMS data is moved from our own code to that using WMS’s that are stored and handled in Mapbuilder. The SRIDs for the WMS’s will be equivalent to the 27700 projection and this would explain why the data appears offset when displayed against the WMS map background.
Regards,
Jonny
____________________
Correspondence
25.01.2017 10:17:51 KJARMALA
Hi all,
We did an upgrade of TWC to 4.7.0.0 over the weekend, and you may have heard that we ran into some issues with SRIDS needing to be changed to correct a coordinate offset issue. This is a summary of where we are. I’ve logged a P1 ticket to track the issue (7000577169).
After the 4700 upgrade and fixes on Sunday, we noticed that the Y coordinate was off by about 15 metres. This would cause issues for Virgin because they’d have users issuing incorrect coordinates to councils, potentially resulting in fines. By luck this happened at FM Conway when we took them on to 4.7.0.0 so I have some scripts that Jonny put together (attached). So we set about running these scripts Sunday afternoon. They took 45 minutes on FMC, which is a much smaller data set than TWC. At present on TWC the first script (chg_srid_to_27700.sql) has been running for 18 hours and is 75% of the way through. We project that it’s going to take until 14:00 UK time, and then we still have to run script 2.
So a couple of things…
The customer status is this: Phil Devereux is happy for us to continue running the scripts and to press on with intention of going live rather than us switching back to our 4.5.0.0 backup. Maps and web services are currently down so in effect Virgin are in a shutdown today. Phil is going to contact all the councils to say they are down so as to hopefully put off any fines.
This SRID business: it came as a surprise on FM Conway when this happened, and we didn’t have anything else to gauge this by at that time. But it seems that this something which happens when going to 4.7.0.0, maybe not the Exor scripts as such, but somewhere in the process. The SRID in use in the UK was always 81989, but this is now incorrect on 4.7.0.0 on 11.2.0.4. Is this expected and understood? Like I say, FMC seemed to be a surprise but with TWC showing the same offset issue, is this something that is known to happen in the upgrade to 4.7.0.0?
Best regards,
Gary
SCRIPTS ATTACHED
____________________
Correspondence
05.12.2016 09:51:13 SRMGRRFC
Service Request created from the web by Gary French
Hi, we upgraded Virgin Media's production TWC system to 4.7.0.0 NET and TMA o
ver the weekend. In our testing on Sunday, we noticed that the Y coordinates were off by about 15 metres which means the users will
get fines for entering in incorrect data. We have seen this before (FMC) and so have scripts to update the SRID on themes from 819
89 to 27700. The first of these scripts (see Jonny) has been running for 18 hours and is 74% of the way through. So the issue is (
hopefully) being resolved, but we need to understand more about why the SRIDs have changed between 4.5 and 4.7. e.g. is it an exor o
r an oracle requirement? If this is necessary for 4.7.0.0 then is there a better way of handling this in the upgrade?
-------------------
Rob's email:
Jonny,
I would not have thought that the move to fix 8 wold cause any problems.
Are they saying that the data with SRID 81989 does not line up with WMS layers that are configured (probably) with the 27700 SRID – if so, I would expect this. The Y ordinate being out by 15m, depends on the location I think.
WMS data for this country would normally be the EPSG SRID of 27700 which is the one used by OS. The SRID of 81989 should never have been used, I never understood why, even after Oracle 11 improved the SRIDs, Aileen went round everywhere and changed them all to 81989. I think she was the one who raised a bug at Oracle to say 81989 was 5 to 10 meters different. To understand why the issues become apparent after the inclusion of the Fix 8 I would need to look into the WMS layers. I would suspect that the WMS layers on the themes post-fix8 would be rendered exactly as they are generated, i.e. using their native SRID. There is a chance that MapViewer made an implicit conversion or, possibly, the WMS was configured to be consistent with 81989, I just do not know. If so, this would account for the disparity. Incidentally, we never found out what happened when customers used an 81989 SRID posted XY data to GeoPlace – but it is likely that NSG data in some areas is out of alignment.
I believe that the change to 27700 should have been planned and with some thought, the layers could have been configured as read-only for a period of time whilst an official script generated a copy of each layer with the new SRID. They should be read-only anyway during the process. Also, a fully supported upgrade script would probably be best executed with batch updates with commits to restrict the use of resources. Also, as we discussed the other day, scripts such as these over big areas, would be best handled without the spatial indexes.
In relation to the scripts, I do not know which scripts are being used, there were so many of them. One script didn’t turn off TMA triggers – so for every geometry which was switched to 27700, a trigger fired and made a new point geometry available (with 27700 SRID) only for the point layer to be updated more efficiently in bulk – this was on three tables and accounted for a lot of the execution time.
I guess it’s a bit late for these matters now though. If you have the scripts, I will take a quick look.
Rob
From: Jonny Heald
Hi Rob,
With recent moves to 4700 for Managed Service customers there have been issues with spatial feature offsetting in Locator that we have seen in the past with the mixing of SRIDs. Please see the thread below for details. Can you please confirm that with NM 4700 fix 8 applied and the move to the handling of WMS data in Oracle Mapbuilder that this is the reason for the behaviour? The changing of 81989 data to 27700 resolves the issue.
Thanks,
Jonny