internal static string GenerateGetGeomEnvelopeInBBoxScript(string tableName, BoundingBox bbox, string geomWhereClause, string geomFieldName = GEOMETRY_FIELD_NAME_DEFAULT, string indexName = GEOMETRY_INDEX_NAME_DEFAULT) { string strQuery = @"DECLARE @bbox geometry DECLARE @res float SET @bbox = geometry::STGeomFromText('{0}',4326) SELECT {1} AS geom FROM {2} WITH(INDEX({3})) WHERE {4}.STIntersects(@bbox) = 1"; // string strQuery = //@"DECLARE @bbox geometry DECLARE @res float // //SET @bbox = geometry::STGeomFromText('{0}',4326) //SET @res = (@bbox.STPointN(1).STY - @bbox.STPointN(2).STY) / 256 // //SELECT {1}.Reduce(@res).STAsBinary() AS geom //FROM {2} WITH(INDEX({3})) //WHERE {1}.STIntersects(@bbox) = 1"; return(string.Format(strQuery, SqlServerModel.GeometryFromBoundingBox(bbox).ToString() , geomFieldName , tableName , indexName , geomWhereClause)); }
internal static string GenerateGetGeomAndIdInBBoxScript(string table, BoundingBox bbox) { string strQuery = @"DECLARE @bbox geometry DECLARE @res float SET @bbox = geometry::STGeomFromText('{0}',4326) SELECT ID,geom_geom FROM COMMUNE WITH(INDEX(IDX_geom_geom)) WHERE geom_geom.STIntersects(@bbox) = 1"; return(string.Format(strQuery, SqlServerModel.GeometryFromBoundingBox(bbox).ToString())); }
internal static string GenerateGetGeomIdInBBoxScript(string tableName, BoundingBox bbox, string idFieldName = PK_FIELD_NAME_DEFAULT, string geomFieldName = GEOMETRY_FIELD_NAME_DEFAULT, string indexName = GEOMETRY_INDEX_NAME_DEFAULT) { string strQuery = @"DECLARE @bbox geometry SET @bbox = geometry::STGeomFromText('{0}',4326) SELECT {4} FROM {2} WITH(INDEX({3})) WHERE {1}.STIntersects(@bbox) = 1"; return(string.Format(strQuery, SqlServerModel.GeometryFromBoundingBox(bbox).ToString() , geomFieldName , tableName , indexName , idFieldName)); }
internal static string GenerateGetGeomInBBoxScript(string tableName, BoundingBox bbox, string geomFieldName = GEOMETRY_FIELD_NAME_DEFAULT, string indexName = GEOMETRY_INDEX_NAME_DEFAULT) { string strQuery = @"DECLARE @bbox geometry DECLARE @res float SET @bbox = geometry::STGeomFromText('{0}',4326) --SET @res = (@bbox.STPointN(1).STY - @bbox.STPointN(2).STY) / 256 --SELECT {1}.Reduce(@res).STAsBinary() AS geom SELECT {1}.STAsBinary() AS geom,[ID] ,[ID_GEOFLA] ,[CODE_DEPT] ,[NOM_DEPT] ,[CODE_CHF] ,[NOM_CHF] ,[X_CHF_LIEU] ,[Y_CHF_LIEU] ,[X_CENTROID] ,[Y_CENTROID] ,[CODE_REG] ,[NOM_REGION] ,[geom_geom] ,[geom_geog] FROM {2} WITH(INDEX({3})) WHERE {1}.STIntersects(@bbox) = 1"; // string strQuery = //@"DECLARE @bbox geometry DECLARE @res float // //SET @bbox = geometry::STGeomFromText('{0}',4326) //SET @res = (@bbox.STPointN(1).STY - @bbox.STPointN(2).STY) / 256 // //SELECT {1}.Reduce(@res).STAsBinary() AS geom //FROM {2} WITH(INDEX({3})) //WHERE {1}.STIntersects(@bbox) = 1"; return(string.Format(strQuery, SqlServerModel.GeometryFromBoundingBox(bbox).ToString() , geomFieldName , tableName , indexName)); }