WorldMapResource_IMPORT.sql

This script is used to mass-import nodes into the World Map resource from NPM 10.6+ and SAM 6.0+ (Nodes with the same coordinates will by dynamically grouped also)

ASSUMPTION - Latitude and Longitude custom properties have been assigned for all Nodes you want in your map

***AS ALWAYS*** BACKUP YOUR DATABASE BEFORE MAKING ANY CHANGES

INSTRUCTIONS:

1) Download file

2) Import/Open in SQL Studio Manager (or other SQL management program)

3) Execute Script in pieces (annotated by Step 1, Step 2, etc in script)

4) Enjoy

- ZackM

http://www.loop1systems.com

WorldMapResource_IMPORT.sql

Find more posts tagged with

Loop1

import

world_map

world_map_resource

import_world_map

loop1systems

content_exchange

custom_sql

custom

Accepted answers

All comments

dba-one

Have you had any issues with some areas appearing on the map but with no information about the nodes? I've loaded a few records using a method based on the script. All node information shows up for one location but no node information for the other two. I've run a detailed trace on the SQL run on the back end and I can't find anything that points to the problem.

zackm

I saw that once when developing the process, but haven't been able to replicate it since. (I've run it in about 6-7 other environments since inception)

dba-one

Zack,

Thanks for getting back to me. I've managed to solve the problem, but I'm not altogether sure why something's work and others do not with the insert statement provided in your script. All map points appear on the map, but some nodes show no information. I went in to manage map and selected one of the problem node records. Over to the right the nodes that should show in the tooltip appear as the should. I clicked edit and went through the motions as if I were adding them manually, but I didn't really make any changes, just saved the record. The result of that was the node info showed as expected on the map point. Hmmm.

I was running a detailed trace on the session and I found this statement:

Exec sp_executesql N'UPDATE dbo.WorldMapPoints

SET [Latitude]=@v0,[Longitude]=@v1

WHERE [Instance]=@k0_0 AND [InstanceID]=@k0_1

OR [Instance]=@k1_0 AND [InstanceID]=@k1_1

OR [Instance]=@k2_0 AND [InstanceID]=@k2_1';,

N'@v0 float,@v1 float,@k0_0 nvarchar(11),@k0_1 int,@k1_0 nvarchar(11),@k1_1 int,@k2_0 nvarchar(11),@k2_1 int',

@v0=xxxxx,@v1=xxxxx,@k0_0=N';Orion.Nodes',@k0_1=xxxxx,@k1_0=N';Orion.Nodes',@k1_1=xxxxx,@k2_0=N';Orion.Nodes',@k2_1=xxxxx

(I have redacted coordinates and node info)

I deleted the node records from the world map table and reinserted them using the initial statement. They didn't show on the map. Then I ran the update statement shown above and they appeared. Interesting. I can't figure out why these dynamic SQL statements do the trick while traditional statements do not. I think it has something to do with the NVarchar data type but I can't figure out why.

So, having grown tired of the struggle I decided to just write my own dynamic statement. The statement below needs to be executed in one window with the results copied to a new one and executed. If someone wants to tweak this and put it in a loop for automatic execution then have at it. The statement is loosely based on the update seen above. I was working with three node groups with two not showing any detail on the map. I deleted all records for the test, ran the statement below to build the inserts and ran them. Bam! all my node info appears on the maps. Not sure why, but I don't care really as I'm past the problem and on to other things.

select

'exec sp_executesql N''insert dbo.WorldMapPoints(Instance, InstanceID, Latitude, Longitude)

values ('+''''+''''+'Orion.Nodes'+''''+''''+', '+cast(n.NodeID as varchar(10))+', '

+ltrim(str(n.Latitude,25,13))+', '+ltrim(str(n.Longitude,25,13))+')'+''''

FROM Nodes n WHERE n.Latitude IS NOT NULL AND n.Longitude IS NOT NULL