我正在使用以下原生sql查询获取多边形中的站点。它给了我一些网站ID。
SELECT s.siteID from Sites s where st_contains( (SELECT polygon from Region where RegionId=9 ) , point( s.latitude,s.longitude ) )=1 ;
但当我用教义做同样的事情时，它给了我空的阵列。
$queryBuilder = $this->createQueryBuilder('s');
$queryBuilder
->where('ST_Contains( :polygon, point(s.latitude, s.longitude) ) = 1')
->setParameter('polygon', $region->getPolygon());
print_r($queryBuilder->getQuery()->getResult());
$region->getPolygon（）给我以下输出
CrEOF'Spatial'PHP'Types'Geometry'Polygon Object
(
[rings:protected] => Array
(
[0] => Array
(
[0] => Array
(
[0] => 48.806863
[1] => 17.050781
)
[1] => Array
(
[0] => 47.813155
[1] => 17.116699
)
[2] => Array
(
[0] => 47.457809
[1] => 16.765137
)
[3] => Array
(
[0] => 46.920255
[1] => 16.237793
)
[4] => Array
(
[0] => 46.664517
[1] => 15.666504
)
[5] => Array
(
[0] => 46.589069
[1] => 14.47998
)
[6] => Array
(
[0] => 47.249407
[1] => 14.282227
)
[7] => Array
(
[0] => 47.635784
[1] => 14.677734
)
[8] => Array
(
[0] => 48.210032
[1] => 15.578613
)
[9] => Array
(
[0] => 48.618385
[1] => 15.864258
)
[10] => Array
(
[0] => 48.748945
[1] => 16.721191
)
[11] => Array
(
[0] => 48.806863
[1] => 17.050781
)
)
)
[srid:protected] =>
)
在花了一天的时间之后，我发现了这个问题。setParameter()有第三个参数，我们可以提到参数的数据类型。
->setParameter('polygon', $region->getPolygon());
这里的:polygon数据类型是字符串，而不是多边形。
你们可以用下面的方法来做。
$queryBuilder = $this->createQueryBuilder('s');
$queryBuilder
->where('ST_Contains( :polygon, point(s.latitude, s.longitude) ) = 1')
->setParameter('polygon', $region->getPolygon(), 'polygon');
print_r($queryBuilder->getQuery()->getResult());
若您不知道数据类型"polygon"的关键是什么。然后使用print_r(Type::getTypesMap());获取添加的所有数据类型的列表。
就我而言，是
Array
(
[array] => Doctrine'DBAL'Types'ArrayType
[simple_array] => Doctrine'DBAL'Types'SimpleArrayType
[json_array] => Doctrine'DBAL'Types'JsonArrayType
[object] => Doctrine'DBAL'Types'ObjectType
[boolean] => Doctrine'DBAL'Types'BooleanType
[integer] => Doctrine'DBAL'Types'IntegerType
[smallint] => Doctrine'DBAL'Types'SmallIntType
[bigint] => Doctrine'DBAL'Types'BigIntType
[string] => Doctrine'DBAL'Types'StringType
[text] => Doctrine'DBAL'Types'TextType
[datetime] => Doctrine'DBAL'Types'DateTimeType
[datetimetz] => Doctrine'DBAL'Types'DateTimeTzType
[date] => Doctrine'DBAL'Types'DateType
[time] => Doctrine'DBAL'Types'TimeType
[decimal] => Doctrine'DBAL'Types'DecimalType
[float] => Doctrine'DBAL'Types'FloatType
[binary] => Doctrine'DBAL'Types'BinaryType
[blob] => Doctrine'DBAL'Types'BlobType
[guid] => Doctrine'DBAL'Types'GuidType
[geometry] => CrEOF'Spatial'DBAL'Types'GeometryType
[point] => CrEOF'Spatial'DBAL'Types'Geometry'PointType
[polygon] => CrEOF'Spatial'DBAL'Types'Geometry'PolygonType
[linestring] => CrEOF'Spatial'DBAL'Types'Geometry'LineStringType
)