Spatial T-SQL that can save you time in Alteryx

So you are a wizard at creating points and doing your spatial analysis and saving your dataset to SQL. However have you tried to use T-SQL to create points within your databases? There are a number of reasons why this may be useful. One being time savings, two that your server may be virtual and no longer physical, this certainly saved us some time publishing data in that type of environment.

For this example I am going to download Geolytix Open Retail Points. Its a great dataset for playing with and I recommend you have a view.

Preview of the dataset:

Geolytix Retail Points view ~ 14000 records

So I am going to step you through a few points to achieve the following:

Alteryx Workflow to load the points into SQL Server
  1. Bring in the data and view the columns.
  2. Understand that your points will be plotted using a specific projection. If you don’t get that right, you will see that points are not over the buildings, or perhaps are in the sea. (Usually a big giveaway!) The projection in this case is WGS1984 (SRID: 4326), however if you wanted to play with British National Grid (SRID: 27700), you can do as the co-ordinates for both are present in the dataset. You will need this later.
  3. Simply add a Record ID tool. (Nice and easy)
  4. Output your data to SQL using the output tool. Add the output tool but in the POST Create SQL Statement box, add the following.
Head to the Post Create SQL Statement on the Output tool connected to SQL Server

5. Copying in the following code, ensuring that your Record ID is called “RecordID” and your spatial object / geography columns are in fact called “Lat_wgs” and “Long_wgs” and your table name is going to be “dbo.Geolytix_Retail_Points”. If this isn’t the case, make sure you amend before you hit run.

–Set Record ID to be not null in preparation for making it a Primary Key
ALTER TABLE dbo.Geolytix_Retail_Points
ALTER COLUMN [RecordID] int not null;
 
–Create a Primary Key on RecordID
ALTER TABLE  dbo.Geolytix_Retail_Points
ADD CONSTRAINT [PK_Geolytix_Retail_Points] PRIMARY KEY CLUSTERED ([RecordID] ASC);
 
–Add a Geography Column for the Spatial Data
ALTER TABLE dbo.Geolytix_Retail_Points
ADD [SpatialObj] Geography;
 
–Update the Spatial Column with Lat / Long and using the correct SRID 4326 = WGS 1984
UPDATE dbo.Geolytix_Retail_Points
SET SpatialObj = geography::Point([Lat_wgs], [Long_wgs], 4326);


Post SQL box with the code in.

Once you are ready hit run and you should get success with results looking like this:

As long as these statements don’t turn up RED you are all good!

So … CRUNCH TIME >>>> Results

Alteryx creating the points, using SQL Server to do the job with post SQL code = 8.9 Seconds!

Alteryx creating the points, using “Create Points” and writing it to the SQL Server = 2:18 Minutes!

As you can see this is a noticeable difference, and its all infrastructure dependent so your results will be different too. Inserting around 14,000 points into a SQL database in less than 10 seconds spatially enabled is perfect. You could always expand this to spatially index your data too.

The best part of the Alteryx platform is its totally versatile for your needs and can meet your needs if you are a coder or not. Hopefully this helps those who are not traditional coders achieve their goals at super lightening speeds. Any questions please leave a comment below, and yes there are hidden mickey’s. You can find me on Twitter, Alteryx Community or right here. Thanks for catching up with us.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s