Convert XY Values to Web Mercator WKID Migration

The Convert XY Values to Web Mercator WKID tool re-projects all work activity coordinates to the WGS_1984_Web_Mercator_Auxiliary_Sphere (102100/3857) spatial reference so that Z values can be stored. Going forward, the WKID cannot be changed because storing coordinates in Web Mercator is the standard. In 15.7, this migration is optional.

PREREQUISITES:

  • The database must be updated to V16_0 at minimum to move forward with the migration.

IMPORTANT: Run this tool on a test environment first to make sure the migration is successful before applying it to the production site.

  1. Select Converts XY values to Web Mercator WKID.
  1. Click Next.

The tool finds any records that need to be updated to the Web Mercator spatial reference. First it changes any X coordinate that is 0 or -9999 to Null.

  1. Click Select All Tables.
  2. Click Process Main.

Once the tables are processed, the tool displays which tables were successfully updated, and which tables have records that couldn't be projected. These values are updated into temporary tables at first so that you can verify that things were projected successfully, and fix any records that couldn't be projected. If a mistake was made, you can select individual tables and click Drop Temp to drop the temporary tables so you can restart work on those tables.

If any values were not able to be projected, they need to be reconciled either using this tool or directly in the database.

NOTE: The CwSvrSearchDefinition, EventHook, and SavedQuery tables cannot be edited in the Data Migration Tool and should be manually edited after everything else has been re-projected and completed in this tool.

  1. To edit values in the Data Migration Tool, click Edit Temp.
  2. Select a table from the drop-down list.
  3. If the values look like valid X and Y values, but were unable to be projected for some reason (for example, the domain ID could not be found), select the Source WKID from the drop-down list or manually enter the correct WKID and click Project.

TIP: If there are records from different domains that have separate WKIDs, use <Ctrl + click> or <Shift + click> to individually select records a part of the same domain that share the same WKID and click Project for each batch of selected records.

  1. If there are bad values that you don't want to project, select the record and click Set XY as Null.
  2. Once all the records have been updated, click Commit to Temp Table.

The values are projected into Web Mercator and are committed to the temporary table.

  1. Perform steps 5-8 for each table that has records that couldn't be processed.
  1. To edit the record directly in the database, copy the select statement and use it to search for the record.
  2. When you are satisfied with the changes you've made, select the desired tables and click Commit Temp to Main.

  1. Click Yes.

TIP: You can commit individual tables to the main database, or commit them all at once.

  1. Once all of the tables have been committed to the main table, click Complete.

  1. Click Yes.

This updates the WKID to Web Mercator in all of the tables.

For users who want to use the Z coordinate values, there is a DefaultZCoordinate preference that was added. This sets the Z coordinate value to 0 as a default. An API call may be used to change this default value to something else. See the sample API call below:

  • <host>/<site>/Services/AMS/Designer/UpdatePreference?data={"Preference":"DEFAULTZCOORDINATE","Value":"365"} for a default Z value of 365 meters in VCS Wkid 115700.

  1. To return to the Cityworks Data Migration window, click Back.
  2. Click Reset to go to the Data Migration login page.
  3. To exit Data Migration, click Exit.