I am fetching sites which comes within a polygon, using following native sql query. And its giving me number of site ids.
SELECT s.siteID from Sites s where st_contains( (SELECT polygon from Region where RegionId=9 ) , point( s.latitude,s.longitude ) )=1 ;
But when I am doing same thing with doctrine, its giving me empty array.
$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() Giving me following output
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] =>
)
After spending 1 more day on this, I found the issue. setParameter()
has third parameter, we can mention data type of parameter.
->setParameter('polygon', $region->getPolygon());
here :polygon
datatype was string, not polygon.
You can do it in following way.
$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());
If you don't know what exactly key is for datatype 'polygon' is.
Then use print_r(Type::getTypesMap());
to get list of all dataypes added.
In my case it was
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
)