Migrating Geolocation Data

Laserfiche 10.3 introduces the ability to search for documents on a map using new geography searches (e.g., search for documents within a certain radius). In prior versions, administrators configured a field to hold the geolocation data on the Laserfiche Mobile Configuration page in the Geotag section under Connection. This field data needs to be migrated to a new dedicated SQL table for the old geolocation data to be searchable using the new geography related searches added in Laserfiche 10.3.

To migrate geolocation data

You can migrate the data in two ways:

Command Line

Run the following command as a user with rights to edit the Laserfiche repository SQL database. Replace $(Param) with 'YourGeotagFieldName'. The command switches are case sensitive and single quotes reside within double quotes for the GeoTag value.

SQLCMD -S "YourSqlServer.example.com" -d "YourLaserficheRepoDatabase" -v GeoTag="'YourGeotagFieldName'" -i "geo.sql"

The geo.sql file is installed by default to the installation folder: C:\Program Files\Laserfiche\Mobile\geo.sql

SQL Server Management Studio

Copy and paste the following syntax into SQL Server Management Studio to execute it directly.

SET QUOTED_IDENTIFIER ON
GO
INSERT INTO tocgeo (tocid, geo1, text1)
SELECT tocid, geography::STPointFromText('POINT(' +
RIGHT(short_str_val, LEN(short_str_val) - CHARINDEX(',', short_str_val)) + ' ' +
LEFT(short_str_val, CHARINDEX(',', short_str_val)-1) + ')', 4326).ToString(), ''
FROM propval pv JOIN propdef pd ON pv.prop_id = pd.prop_id
WHERE pd.prop_name = $(GeoTag) AND short_str_val IS NOT NULL AND short_str_val != '' AND CHARINDEX(',', short_str_val) > 1
AND NOT EXISTS (SELECT * FROM tocgeo t WHERE t.tocid = pv.tocid)
GO
SET QUOTED_IDENTIFIER OFF
GO

Note: The GeoTag field is per repository. If you are using a GeoTag field in more than one repository, you will need to run this script for each repository that has a GeoTag field configured.

Note: Integrations relying on the previous GeoTag field will continue to work correctly and data will not be cleared from those fields. Populating the previous fields with new locations will also continue to work, but location related searches will only be available on previous locations if they are migrated using this script.