Not getting expected output from doctrine query as getting mysql native sql query, I am using doctrine2-spatial extension

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] => 
)

Answers


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
)

Need Your Help

Change date's format after loading it to UserForm textbox

excel vba date excel-vba

I have a userform with textbox. When textbox is initialized it's getting filled with actual date. What I want to do is to fill it with custom date format = DD-MM-YYYY

file upload not working in chrome

javascript html5 google-chrome javascript-events file-upload

I am uploading an image from my PC. It is working fine in firefox. But in chrome, the dialog to select the file doesn't open up! I'm invoking click event of input type in javascript.