MapInfo Pro

Expand all | Collapse all

Run spatial query in SQLServer against MI tables?

  • 1.  Run spatial query in SQLServer against MI tables?

    Posted 15 days ago
    Edited by Miroslav Kovacevic 14 days ago
    Hello,

    I was wondering is it possible to run queries against MI tables directly on SQL Server.

    For example if I have a query I run in MI:
     SELECT * FROM POINTS, POLYGON WHERE POINTS.OBJ WITHIN POLYGON.OBJ AND POLYGON.ID = 1

    What would look like query in SQL Server?

    I have read Microsoft documentation on Spatial Data Types, however I'm not skilled enough to translate this to work with MI tables.

    They usually give some example like this:
    DECLARE @g geometry;
    DECLARE @h geometry;
    SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
    SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
    SELECT @g.STContains(@h);

    I tried to set geometry with:
    SET @g = Points.SP_GEOMETRY
    but it doesn't work. Plus,  their examples are always for querying one object against another, not objects from one table against objects from another table.

    Thank you,
    Miroslav

    Edit: I found it in Peter's slides 'Taking Advantage of a Spatial Database with MapInfo Professional'

    It would look something like this:
    SELECT Pt.* FROM POINTS Pt, POLYGONS P WHERE Pt.SP_GEOMETRY.STWithin(P.SP_GEOMETRY)=1 AND P.ID = 1

    ----------------------------
    Miroslav Kovacevic
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Run spatial query in SQLServer against MI tables?

    Posted 14 days ago
    Hi Miroslav,

    You need a couple of minor corrections to your SQL:

    SELECT Pt.* FROM POINTS Pt, POLYGONS P WHERE Pt.SP_GEOMETRY.STWithin(P.SP_GEOMETRY)=1 AND P.ID = 1

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 3.  RE: Run spatial query in SQLServer against MI tables?

    Posted 14 days ago
    James,

    you are right. I have edited post and corrected syntax. Thank you.

    ------------------------------
    Miroslav Kovacevic
    Knowledge Community Shared Account
    ------------------------------