Western Mass Hosting's .Net Articles

Geographic Location Lookup System for SQL Server and MySQL

Introduction to Geographic Data Management

When working with location-based data across different database platforms, understanding how to efficiently store and query geographic information is essential. This guide provides comprehensive implementations for both Microsoft SQL Server and MySQL, focusing on a flexible geographic area lookup system rather than just store locations. The solutions presented here can be adapted for various use cases including service area identification, regional analysis, or population distribution studies.

Database Schema Implementation

Microsoft SQL Server Geographic Table Structure

SQL Server’s robust geography data type provides excellent support for spatial operations. This implementation uses a computed column to automatically generate geographic points from latitude/longitude coordinates:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [Geography].[LocationPoints](
    [LocationID] [bigint] IDENTITY(1,1) NOT NULL,
    [LocationCode] [varchar](20) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [LocationName] [varchar](255) NULL,
    [Region] [varchar](100) NULL,
    [AdminDivision] [varchar](255) NULL,
    [LocationType] [varchar](100) NULL,
    [GeoPoint] AS ([geography]::Point([Latitude],[Longitude],(4326))) PERSISTED,
 CONSTRAINT [PK_LocationPoints] PRIMARY KEY CLUSTERED 
(
    [LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

MySQL Spatial Data Table Structure

MySQL’s spatial extensions provide similar functionality with a slightly different syntax:

CREATE TABLE `LocationPoints` (
    `LocationID` bigint NOT NULL AUTO_INCREMENT,
    `LocationCode` varchar(20) DEFAULT NULL,
    `Latitude` double DEFAULT NULL,
    `Longitude` double DEFAULT NULL,
    `LocationName` varchar(255) DEFAULT NULL,
    `Region` varchar(100) DEFAULT NULL,
    `AdminDivision` varchar(255) DEFAULT NULL,
    `LocationType` varchar(100) DEFAULT NULL,
    `GeoPoint` POINT NOT NULL SRID 4326,
    PRIMARY KEY (`LocationID`),
    SPATIAL INDEX(`GeoPoint`),
    INDEX (`LocationCode`),
    INDEX (`Region`),
    INDEX (`LocationType`)
) ENGINE=InnoDB;

Geographic View Implementations

SQL Server Optimized View

CREATE VIEW [Geography].[LocationView] WITH SCHEMABINDING
AS
SELECT 
    LocationID, 
    LocationCode, 
    Latitude, 
    Longitude, 
    LocationName, 
    Region, 
    AdminDivision, 
    LocationType, 
    GeoPoint
FROM Geography.LocationPoints
GO

MySQL Geographic View

CREATE VIEW `LocationView` AS
SELECT 
    LocationID, 
    LocationCode, 
    Latitude, 
    Longitude, 
    LocationName, 
    Region, 
    AdminDivision, 
    LocationType, 
    GeoPoint
FROM LocationPoints;

Radius Search Procedures

SQL Server Geographic Search Procedure

This procedure finds all locations within a specified radius of a given point:

CREATE PROCEDURE [Geography].[FindLocationsInRadius]
    @ReferenceCode VARCHAR(20),
    @RadiusKm FLOAT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @lat FLOAT;
    DECLARE @lon FLOAT;
    DECLARE @referencePoint GEOGRAPHY;

    SELECT @lat = Latitude, @lon = Longitude 
    FROM Geography.LocationPoints 
    WHERE LocationCode = @ReferenceCode;

    SET @referencePoint = geography::Point(@lat, @lon, 4326);

    SELECT 
        LocationID, 
        LocationCode, 
        LocationName, 
        Region, 
        AdminDivision,
        (@referencePoint.STDistance([GeoPoint]) / 1000) AS DistanceKm
    FROM Geography.LocationPoints
    WHERE @referencePoint.STDistance([GeoPoint]) <= (@RadiusKm * 1000)
    ORDER BY DistanceKm;
END
GO

MySQL Radius Search Procedure

DELIMITER //
CREATE PROCEDURE `FindLocationsInRadius`(
    IN p_ReferenceCode VARCHAR(20),
    IN p_RadiusKm FLOAT
)
BEGIN
    DECLARE v_lat DOUBLE;
    DECLARE v_lon DOUBLE;
    DECLARE v_point POINT;

    SELECT Latitude, Longitude INTO v_lat, v_lon 
    FROM LocationPoints WHERE LocationCode = p_ReferenceCode LIMIT 1;

    SET v_point = ST_SRID(POINT(v_lon, v_lat), 4326);

    SELECT 
        LocationID, 
        LocationCode, 
        LocationName, 
        Region, 
        AdminDivision,
        (ST_Distance_Sphere(v_point, GeoPoint) / 1000) AS DistanceKm
    FROM LocationPoints
    WHERE ST_Distance_Sphere(v_point, GeoPoint) <= (p_RadiusKm * 1000)
    ORDER BY DistanceKm;
END //
DELIMITER ;

Advanced Geographic Queries

Finding Locations Within a Boundary (SQL Server)

CREATE PROCEDURE [Geography].[FindLocationsWithinArea]
    @Boundary GEOGRAPHY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        LocationID, 
        LocationCode, 
        LocationName, 
        Region,
        AdminDivision
    FROM Geography.LocationPoints
    WHERE @Boundary.STContains(GeoPoint) = 1
    ORDER BY LocationName;
END
GO

MySQL Polygon Area Search

DELIMITER //
CREATE PROCEDURE `FindLocationsWithinPolygon`(
    IN p_PolygonWKT TEXT
)
BEGIN
    DECLARE v_polygon POLYGON;

    SET v_polygon = ST_GeomFromText(p_PolygonWKT, 4326);

    SELECT 
        LocationID, 
        LocationCode, 
        LocationName, 
        Region,
        AdminDivision
    FROM LocationPoints
    WHERE ST_Within(GeoPoint, v_polygon) = 1
    ORDER BY LocationName;
END //
DELIMITER ;

Implementation Considerations

When implementing a geographic lookup system, several important factors should be considered:

Data accuracy is paramount - ensure your coordinate data comes from reliable sources and is properly validated. The spatial reference system (SRID 4326 in these examples) should be consistently applied throughout your application to prevent calculation errors.

Performance optimization requires proper indexing strategies. Both SQL Server and MySQL benefit from spatial indexes on the geographic columns, but remember that spatial indexes behave differently than traditional indexes. Regular monitoring and query optimization are essential as your dataset grows.

For applications requiring high precision over large distances, consider more advanced geographic libraries or extensions that account for geodetic calculations. While the built-in functions provided by SQL Server and MySQL work well for most use cases, specialized scenarios might require additional mathematical considerations.

The system's flexibility allows adaptation to various geographic lookup needs. By modifying the stored procedures or adding additional parameters, you can implement features like:

• Multi-point route analysis
• Density heatmaps
• Regional clustering
• Proximity alerts

Remember that geographic calculations are computationally intensive. Implement appropriate caching strategies and consider pre-calculating frequently needed results where possible. For web applications, implementing these calculations at the database level rather than in application code generally provides better performance.