Skip to content
Aug 21 15

SQL Server – Management Studio Tip – Change Select Top 1000 Rows command

by manoj.desai

If you use SQL Server Management Studio, you know how annoying it is when you try to select records from a table and the UI limits you to the Top 1000 records.

Well there is a way to change it!!!!

In SQLS Enterprise Management Studio, go to Tools > Options > SQL Server Object Explorer > Commands and change the values as you see fit.

2015-08-21_13-56-59

 

May 3 13

Making Reprojected Raster Tiles Work with FME

by troy.miller

One of the problems I have come across is taking a set of raster files in a state plane coordinate system and displaying them on a UTM map. The problem comes in when the images are re-projected and it includes a rotation. Extra black pixels are created around the outside so when you bring the tiles together the black edges lay across each other eliminating good sections of imagery. There are various techniques to address this issue like setting nodata values and creating alpha channels but they are not simple and there must be a better way. There must be a method of generating these tiles that is flexible and can be completed with an external hard drive and workstation hardware. I will tell you about my solution to this problem using FME.

Here is an example of a tile set in state plane uncompressed GeoTiff format with a total file size of 817MB. This sample set needs to be converted and compressed for the GIS server. The two format outputs needed are GeoTiff and ECW. Along with this is each tile should line up with the state Township Range and Section area.

Raster Before Reprojection

Raster Before Reprojection

The tiles are square and they lay across each other but the pixels match exactly. Now take that data and reproject it into UTM.

After Reprojection

After Reprojection

You can now see the black edges and the rasters have been rotated. One way to address this is to set a NODATA value on the image so the black area will become see-through.

NODATA Value Set to Black

NODATA Value Set to Black

This looks to have solved it but on closer inspection you can see that anything that was black is now missing from the image.

Black Pixels Missing

Black Pixels Missing

To make matters worse I need to use ER Mapper’s ECW format but the NODATA value is not applicable so the black edges remain in my output ECWs. The file sizes have worked out pretty well though. The ECW set now totals 208MB and my GeoTiffs with JPG compression are at 170MB for a percent compression of 75% and 79% respectively.

The answer to the problem turns out to be simple. Make the new tiles square so the black lines are not created. The next image shows the outline of the reprojected image in blue and the bounding box of that image in green showing the new square tile.

Bounding Box of Reprojected Tile

Bounding Box of Reprojected Tile

Now we can mosaic all the tiles together and cut it up by these new tile boundaries right? If you have 20 tiles then yes, if you have 1800 tiles like this set then no you don’t have enough RAM.

What I did next is I used FME to find all the raster tiles around each tile so I would have a list of images to mosaic and the tile to cut from that mosaic. The output is then saved as an ECW and Geotiff.

FME Script Cuts the Tile

FME Script Cuts the Tile

I created a batch file to call FME and pass in the image names and iteratively loop over each tile. This limited the amount of RAM needed on each run so a standard workstation could complete the task. Start it on Friday evening and it should be ready the next Monday.

Dec 19 11

Using MSSQL Spatial with AutoCAD Map 3D and Mapguide Enterprise

by troy.miller

Spatial databases allow for instant database connectivity just like normal databases but now hold the spatial components. There is no longer a need to organize directories of shape files and keep track of which one is the most current. The database handles this and allows many connections to the same data. Microsoft SQL 2008 is the first version to allow for the storage of spatial data. MSSQL 2008 is a good alternative to Oracle Spatial because its ease of setup especially for those not used to the complexities of Oracle.

The FDO is the final link between the spatial database, AutoCAD Map 3D, and Mapguide Enterprise. The Feature Data Object is a layer of software that allows for standard operations between feature sources like SQL Spatial and client software like AutoCAD Map 3D. The FDO is used to generate the feature stores which include all the tables needed to handle the FDO schema.

Creating the Feature Store

While you could create a schema from scratch, we are going to import a schema and data from an existing SHP file.

Open a new DWG, set the coordinate system, and set the units.

Open the Task Pane > Data > Connect To Data

Select “Add SHP Connection” pick the SHP file to load into SQL Spatial, and click Connect.

The coordinate systems should match. Make sure the SHP feature source is checked and click Add to Map.

AutoCAD will add the SHP file as layer to the map.

Now we are going to add our SQL Spatial connection. With the Data Connection panel still open, click “Add SQL Server Spatial Connection”. Enter the Server name and log in.

Click the Data Store drop down box and select Add New Data Store.

Enter a Data Store Name which will be the Database name in SQL Server.

Select the coordinate system for the spatial data.

Check the box that says “Use FDO enabled schema”. This will allow for optimistic concurrency support in the database.

Click OK to create the data store.

A pop up will ask to edit the schema. Select Edit Later.

Close the Data Connect Panel

In the Task Pane, click the Map Explorer tab.

Click Tools > Bulk Copy…

The Bulk Copy Dialog will appear.

In the From section, select Source > Feature Sources > SHP_1 (Or the name of your SHP connection)

In the To section, select Source > Feature Sources > SqlServeSpatial_1 (Or the name of your SQLS connection)

In the Select items to copy section check the Default box.

This will automatically setup the schema to transfer from the SHP file to the SQL Spatial table.

Set the schema how you would like in the SQL table by unchecking any fields you do not want transferred.

Click Copy Now to start the transfer.

Once it completes you can close out of the dialogs.

Now that it is loaded into SQL Spatial, we need to add it as a layer in AutoCAD.

In the Task Pane click Data > Connect to Data

Click on your SQL Spatial connection (SqlServerSpatial_1)

Make sure the feature type you added to SQL Spatial is checked and click Add to Map.

While in this screen, right click on your SHP connection and disconnect it. The SHP layer is no longer needed.

You now have the SQL Spatial data loaded into AutoCAD

To view attributes, highlight the new layer and click the Table icon

Connecting SQL Spatial to Mapguide Enterprise

This tutorial assumes some knowledge on how to setup a Mapguide Enterprise site with Mapguide Studio. We will be using the OSGeo Fdo provider for SQL Server Spatial.

Create a new Data Connection.

Enter the Server name, login credentials, click Test Connection, and select newly created Data Store.

Save your Data Connection in the Data connections directory

Create a new layer and select your Data Connection as the Data Resource.

Make sure you have the correct Feature Class and theme the layer as desired.

Add the Mapguide layer to a map and view the new layer coming from SQL Server Spatial

Here is the beauty of this setup.

Switch back to AutoCAD Map and move a line.

Right click on the layer and select Check in.

Switch back to Mapguide and pan the screen.

The data is updated instantly.

This tutorial covered how to use SQL Server Spatial to store your spatial data. The FDO tools are used to import an existing SHP file into a SQL Spatial data store. This data can be edited in AutoCAD Map and is instantly available in Mapguide Enterprise.

Jan 11 11

AutoCAD Object Data and FME

by troy.miller

Autodesk’s Object Data is a proprietary internal database to store attributes on various AutoCAD objects. It can be difficult to work with but is still a great way to store attribute information. What makes it difficult to work with is extracting that data especially with the built-in AutoCAD tools.

Safe Software’s Feature Manipulation Engine (FME) plays a pivotal role by bridging the gap between the AutoCAD Object Data and your GIS formats. Using FME to set up your ETL (extract, transform, and load) script can be a bit tricky because of the number of options available to you. The method I choose is based on the ability to control each step of the attribute extraction and merging process.

The Old Way

First lets look at the pre-FME way of extracting data using only the AutoCAD Map 3D tools.

I will be using a sample sewer system with Object data assigned to both the lines and the nodes.

To export our data we must use the Map Tools: Map > Tools > Export …

or Output > Map 3D Export

You must have your DWG setup so you can filter by layer or selection. You cannot save a SHP for lines and a SHP for nodes but you have to repeat the process for each file you wish to create.

The attribute selection dialog lets you select the Object Data table and then hitting OK should result in the file you want. If it doesn’t then fix and repeat.

There is a better way thanks to Safe Software and FME.

The New Way — Creating an ETL Script

Open up FME and start a new workspace.  If you haven’t tried it already, try the Workspace Dialog as it helps simplify a few things.

For the reader, select Autodesk AutoCAD Map 3D Object Data and select your DWG.

For the writer, select your GIS format. In this case I will use SHP.

Under the reader Parameters… select “Raw Relational” and uncheck “Expand Blocks into Entities”

The Raw Relational setting will give us the most control as we start our ETL script by letting us choose exactly how and when the attributes are joined to the objects.

For this example I will only use one layer.

As you can see the geometry feature type is on the top and the Object Data attribute table is down below. We are gong to join them with the Feature Merger transformer. Both feature types have the autocad_od_entity_key attribute that will be used for the join.

Connect the geometry feature type to the requester since it is “requesting” attributes. Connect the Object Data table feature type to the supplier since it is supplying the attributes. Use the autocad_od_entity_key as the join attribute for both.

I connected some visualizers so I could see what else is going on with my join. This is useful when checking the quality of your data. Since I am only using one layer in this example I am missing many objects with Object Data but out of the objects I did process, all of them are complete except for two.

Conclusion

With this simple workspace we have created a SHP file that contains the AutoCAD DWG geometry linked to its associated object data in the form of feature attributes.  This method of using Raw Relational on the AutoCAD Object Data reader allows for much better control of your data. Data quality checks can be put in place to insure every feature and record is accounted for. FME allows this process to be setup once and run many times making the old AutoCAD Map export cumbersome, prone to error, and obsolete.

Nov 2 10

Changing the MapGuide Active Tool Map Cursor

by Romany Saad

At Websoft, we have many clients that use various versions of MapGuide: 6.5, Open Source, Enterprise 2009, 2010 and 2011.

When working with the map, it is easy to forget which tool you are using. For example, you may think that you are in pan mode when you are really in zoom to rectangle mode.  Clicking and dragging the cursor across the map, you will end up zooming instead of panning.

As a result, one of the features that many of our MapGuide Enterprise/Open Source clients have requested is changing the active cursor to specify which toolbar tool is selected.

This feature has since been implemented in MapGuide Enterprise 2011 and MapGuide Open Source 2.1. However, for our clients who have yet to upgrade, we apply the patch manually to previous versions of MapGuide.

Those interested in seeing how this change was implemented originally can view the original discussion, the submitted patch, and the final submitted changeset.

Implementing the Cursors

To implement this change in your own MapGuide deployment, the first step is to download the new cursors.

Then, make a backup of your current cursors/icons directory and extract the cursors from the ZIP file to that directory.
For MapGuide Enterprise, the directory is found here:
C:\Program Files\Autodesk\MapGuideEnterprise2010\WebServerExtensions\www\stdicons
For MapGuide Open Source, the directory is found here:
C:\Program Files\OSGeo\MapGuide\Web\www\stdicons

Then, backup the ajaxmappane.templ and mainframe.templ files found in the viewerfiles directory.
For MapGuide Enterprise, the directory is found here:
C:\Program Files\Autodesk\MapGuideEnterprise2010\WebServerExtensions\www\viewerfiles
For MapGuide Open Source, the directory is found here:
C:\Program Files\OSGeo\MapGuide\Web\www\viewerfiles

Next, find the following nine functions in the ajaxmappane.templ file and add the highlighted lines:

function DigitizePoint(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("Point");
    StartDigitizing(new PointDigitizer(OnShapeDigitized, ScreenToMapUnits), handler);
}

 

function DigitizeLine(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("Line");
    StartDigitizing(new LineDigitizer(OnShapeDigitized, ScreenToMapUnits, cancelTgt, shape, "shapeFeedback", "#000000", mapPosX, mapDevW, mapDevH), handler);
}

 

function DigitizeCircle(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("Circle");
    StartDigitizing(new CircleDigitizer(OnShapeDigitized, ScreenToMapUnits, cancelTgt, shape, "shapeFeedback", "#000000", mapPosX, mapDevW, mapDevH), handler);
}

 

function DigitizeRectangle(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("Rectangle");
    StartDigitizing(new RectangleDigitizer(OnShapeDigitized, ScreenToMapUnits, cancelTgt, shape, "shapeFeedback", "#000000", mapPosX, mapDevW, mapDevH), handler);
}

 

function DigitizeLineString(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("LineString");
    StartDigitizing(new LineStringDigitizer(OnShapeDigitized, ScreenToMapUnits, cancelTgt, shape, "shapeFeedback", "#000000", mapPosX, mapDevW, mapDevH, ShowSelectionTip, selTtipText, false), handler);
}

 

function DigitizePolygon(handler)
{
    if(handler == null)
        return;
    SetDigitizeCursor("Polygon");
    StartDigitizing(new LineStringDigitizer(OnShapeDigitized, ScreenToMapUnits, cancelTgt, shape, "shapeFeedback", "#000000", mapPosX, mapDevW, mapDevH, ShowSelectionTip, selTtipText, true), handler);
}

 

function ExecuteMapAction(action)
{
    if(!mapInit)
        return;
    CancelDigitization();
    switch(action)
    {
        case 1:
            UpdateMapActionCursor(action);
            tool = 4;
            break;
        case 2:
            PanUp();
            break;
        case 3:
            PanDown();
            break;
        case 4:
            PanRight();
            break;
        case 5:
            PanLeft();
            break;
        case 7:
            UpdateMapActionCursor(action);
            tool = 1;
            break;
        case 8:
            UpdateMapActionCursor(action);
            tool = 2;
            break;
        case 9:
            UpdateMapActionCursor(action);
            tool = 3;
            break;
        case 10:
            ZoomSelection();
            break;
        case 11:
            InitialMapView();
            break;
        case 12:
            PreviousView();
            break;
        case 13:
            NextView();
            break;
        case 14:
            InitialMapView();
            break;
        case 15:
            UpdateMapActionCursor(action);
            tool = 0;
            break;
        case 16:
            UpdateMapActionCursor(action);
            tool = 5;
            break;
        case 17:
            UpdateMapActionCursor(action);
            tool = 6;
            break;
        case 19:
            ClearSelection();
            break;
        case 20:
            Refresh();
            break;
        case 21:
            break;
        case 22:
            About();
            break;
    }
}

 

function CancelDigitization()
{
    if(digitizing)
    {
        SetCurrentToolCursor();
        digitizer.Cancel();
        digitizing = false;
        digihandler = null;
    }
}

 

function OnShapeDigitized(shape)
{
    if(digitizing)
    {
        SetCurrentToolCursor();
        digitizing = false;
        if(digihandler != null)
        {
            digihandler(shape);
            digihandler = null;
        }
    }
}

Then, add the following four functions between the existing GotoView and ExecuteMapAction functions.

//Update the map cursor based on a map action
function UpdateMapActionCursor(action)
{
    var strCursor = "";
    switch(action)
    {
        case 1: /*pan*/             strCursor = "pan";          break;
        case 7: /*Zoom plus*/       strCursor = "zoomin";       break;
        case 8: /*Zoom minus*/      strCursor = "zoomout";      break;
        case 9: /*Zoom window*/     strCursor = "zoomrect";     break;
        case 16: /*Select Radius*/  strCursor = "selectRadius"; break;
        case 17: /*Select Polygon*/ strCursor = "selectPolygon";break;
    }
    SetMapCursor(strCursor)
}

 

//Update the map cursor to reflect the current tool
function SetCurrentToolCursor()
{
    var strCursor = "";
    switch(tool)
    {
        case 1: /*Zoom plus*/      strCursor = "zoomin";       break;
        case 2: /*Zoom minus*/     strCursor = "zoomout";      break;
        case 3: /*Zoom window*/    strCursor = "zoomrect";     break;
        case 4: /*pan*/            strCursor = "pan";          break;
        case 5: /*Select Radius*/  strCursor = "selectRadius"; break;
        case 6: /*Select Polygon*/ strCursor = "selectPolygon";break;
    }
    SetMapCursor(strCursor)
}

 

//Update the map cursor for digitization
function SetDigitizeCursor(digitizeTool)
{
    var strCursor = "";
    switch(digitizeTool)
    {
        case "Point":      strCursor = "digitizePoint"; break;
        case "Line":       strCursor = "digitizeLine"; break;
        case "Circle":     strCursor = "digitizeCircle"; break;
        case "Rectangle":  strCursor = "digitizeRectangle"; break;
        case "LineString": strCursor = "digitizeLineString"; break;
        case "Polygon":    strCursor = "digitizePolygon"; break;
    }
    SetMapCursor(strCursor)
}

 

function SetMapCursor(strCursor)
{
    if(strCursor == "")
    {
        strCursor = "auto";
    }
    else
    {
        strCursor = "url('../stdicons/" + strCursor + ".cur'), pointer";
    }
    document.getElementById("tbMap").style.cursor = strCursor;
}

Finally, find the following four functions in the mainframe.templ file and add the highlighted lines:

function EndMeasure()
{
    inMeasure = false;
    try
    {
        GetMapFrame().SetCurrentToolCursor();
        measureWinRef.MeasureEnded();
    }
    catch(e) {}
}

 

function ResumeMeasure()
{
    inMeasure = true;
    try
    {
        GetMapFrame().SetMapCursor("measure");
        measureWinRef.MeasureResumed();
    }
    catch(e) {}
}

 

function StopMeasure()
{
    inMeasure = false;
    GetMapFrame().SetCurrentToolCursor();
}

 

function ExecuteMeasureCommand(index)
{
    if(GetMapFrame().IsDigitizing()) {
        SetStatusMsg("__#DIGALREADYRUNNING#__");
        return;
    }
    inMeasure = true;
    pointBegin = null;
    GetMapFrame().SetMapCursor("measure");
    measureWinRef = OpenUrl("%s?PARTIAL=0&TOTAL=0", index);
}

Conclusion

After editing the two template files and adding the cursors, you should now be able to access your MapGuide map and view the new cursors when selecting specific toolbar tools such as zoom to rectangle in our example:

MapGuide Cursors

The New Zoom to Rectangle Cursor