Distinct on Geographic

  • I have a simple select statement that has two geographic data types (SQL R2).

    How can I make these fields distinct?

    select

    z.country,

    z.city,

    z.state,

    z.Country,

    z.GeogCol1,

    z.GeogColTemp,

    z.zipcode,

    l.data_date,

    l.loan,

    l.mtgrlastname,

    l.mtgrfirstname,

    l.propertystreetnum,

    l.propertydirection,

    l.propertystreet,

    l.cityname,

    l.propertystate

    from ZipCodes

    Msg 421, Level 16, State 1, Line 1

    The geography data type cannot be selected as DISTINCT because it is not comparable.

  • Hi,

    As SQL tells you, you can't use DISTINCT on a geography column. What do you store exactly in your geo columns ? If they are simple points, you can use Geocolumn.Lat and Geocolumn.Long. It will give you two real numbers, which can be used with DISTINCT.

    I hope this helps,

    Laurent

  • You can use STAsBinary() (Returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry instance.)on geometry types to convert into binary format which can be used in distinct.

    You can convert back to geometry by using STGeomFromWKB.

  • Create a CTE that has a ROW_NUMBER column, and then select rows where that column = 1 ROW_NUMBER() = 1

    WITH src AS (

    select

    z.country,

    z.city,

    z.state,

    z.Country,

    z.GeogCol1,

    z.GeogColTemp,

    z.zipcode,

    l.data_date,

    l.loan,

    l.mtgrlastname,

    l.mtgrfirstname,

    l.propertystreetnum,

    l.propertydirection,

    l.propertystreet,

    l.cityname,

    l.propertystate,

    ROW_NUMBER() OVER (PARTITION BY l.propertynumber, l.propertystreet, z.zipcode ORDER BY l.propertynumber) AS idx

    from ZipCodes)

    SELECT * FROM src WHERE idx = 1;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply