CODEWITHSUNDEEP
sql-serversql-server-2008postgresql
kinkajou
kinkajou

Reputation: 3728

Implementing Geometry Columns in SQL Server

The geometry_columns in postgres looks like (question in gis.stackexchange) :

SELECT 
    current_database()::character varying(256) AS f_table_catalog, 
    n.nspname::character varying(256) AS f_table_schema, 
    c.relname::character varying(256) AS f_table_name, 
    a.attname::character varying(256) AS f_geometry_column, 
    COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod), 2),postgis_constraint_dims(n.nspname::text, c.relname::text, a.attname::text), 2) AS coord_dimension, 
    COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), postgis_constraint_srid(n.nspname::text, c.relname::text, a.attname::text), 0) AS srid, 
    replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), postgis_constraint_type(n.nspname::text, c.relname::text, a.attname::text)::text, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
FROM 
    pg_class c, pg_attribute a, pg_type t, pg_namespace n
WHERE 
    t.typname = 'geometry'::name 
    AND a.attisdropped = false 
    AND a.atttypid = t.oid 
    AND a.attrelid = c.oid 
    AND c.relnamespace = n.oid 
    AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char") 
    AND NOT pg_is_other_temp_schema(c.relnamespace) 
    AND NOT (n.nspname = 'public'::name AND c.relname = 'raster_columns'::name) 
    AND has_table_privilege(c.oid, 'SELECT'::text);

The alternative that I am making for SQL Server is:

SELECT  
    c.TABLE_CATALOG
    ,c.TABLE_SCHEMA AS TABLE_SCHEMA
    ,c.TABLE_NAME AS TABLE_NAME
    ,c.COLUMN_NAME AS COLUMN_NAME   
FROM 
    information_schema.columns c
JOIN 
    information_schema.tables t ON c.TABLE_NAME = t.TABLE_NAME
                                AND t.TABLE_TYPE IN ('BASE TABLE','VIEW')
                                AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE 
    c.DATA_TYPE = 'geometry'

But it doesn't have:

I know the query in SQL Server to get value listed above is :

SELECT distinct 
    @column_name.STDimension() as coord_dimension ,
    SP_GEOMETRY.STGeometryType() as srid,
    SP_GEOMETRY.STSrid 
FROM
    schema_name.@table_name

This query needs to query through table to find distinct STDimension since single table can have multiple dimensions.

How do I combine this two query into a single query as in postgres?

Note: I want use to view only table that they have access to and I think querying Information_schema will be enough for it. Am I correct?

Upvotes: 1

Views: 1177

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

I can't speak to postgres' implementation, but those properties are not properties of the column itself in SQL Server. That is, the column doesn't have an SRID, but the individual data within it do. You could, in theory, have two rows each with their geography data in a different SRID (for instance). The same goes for dimension and geometry type. So what you're looking for isn't available as a metadata lookup on the table schema; you'll have to look at the data itself.

Upvotes: 1

Related Questions