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));
}