a point we are testing distances with using a wgs84 coordinate that ll be implicitly transformed to that to the coordinate system of the field epsg 32140 texas south central wunits in meters another one for australia test initialization of distance models test the dwithin lookup type distances all should be equal except for the degreemeter pair in aucities that s somewhat approximate expected cities for australia and texas performing distance queries on two projected coordinate systems one with units in meters and the other in units of u s survey feet with a complex geometry expression now performing the dwithin queries on a geodetic coordinate system result in meters result in units of the field creating the query set a valueerror should be raised on postgis when trying to pass distance objects into a dwithin query using a geodetic field retrieving the cities within a 20km donut wa 7km radius hole thus houston and southside place will be excluded as tested in the test02dwithin above doing a distance query using polygons instead of a point if we add a little more distance 77002 should be included test distance lookups on geodetic coordinate systems line is from canberra to sydney query is for all other cities within a 100km of that line which should exclude only hobart adelaide spatialite is less accurate and returns 102 8km for batemans bay getting all cities win 550 miles of hobart cities that are either really close or really far from wollongong and using different units of distance normal geodetic distance lookup uses distancesphere on postgis geodetic distance lookup but telling geodjango to use distancespheroid instead we should get the same results bc accuracy variance won t matter in this test case with a combined expression with spheroid param with a complex geometry expression dwithin lookup in a subquery using outerref as a parameter qs censuszipcode objects annotate annotatedvalueexists southtexascity objects filter pointdwithinouterrefpoly dm10 filterannotatedvaluetrue self assertequalself getnamesqs 77002 77025 77401 skipunlessdbfeaturesupportsdwithinlookup supportsdwithindistanceexpr def testdwithinwithexpressionrhsself linestring of wollongong and adelaide coords ls linestring150 902 34 4245 138 6 34 9258 srid4326 qs australiacity objects filter pointdwithinls falloweddistance orderbyname self assertequal self getnamesqs adelaide mittagong shellharbour thirroul wollongong skipifdbfeaturesupportsdwithindistanceexpr def testdwithinwithexpressionrhsnotsupportedself ls linestring150 902 34 4245 138 6 34 9258 srid4326 msg this backend does not support expressions for specifying distance in the dwithin lookup with self assertraisesmessagenotsupportederror msg list australiacity objects filter pointdwithinls falloweddistance noqa class distancefunctionstestsfunctestmixin testcase fixtures initial skipunlessdbfeaturehasareafunction def testareaself reference queries select stareapoly from distappsouthtexaszipcode areasqm 5437908 90234375 10183031 4389648 11254471 0073242 9881708 91772461 tolerance has to be lower for oracle tol 2 for i z in enumerate southtexaszipcode objects annotateareaareapoly orderbyname self assertalmostequalareasqmi z area sqm tol skipunlessdbfeaturehasdistancefunction def testdistancesimpleself lagrange geosgeometrypoint805066 295722839 4231496 29461335 32140 houston southtexascity objects annotatedistdistancepoint lagrange orderbyid first tol 2 if connection ops oracle else 5 self assertalmostequalhouston dist m 147075 069813 tol skipunlessdbfeaturehasdistancefunction hastransformfunction def testdistanceprojectedself the point for la grange tx lagrange geosgeometrypoint96 876369 29 905320 4326 reference distances in feet and in meters got these values from using the provided raw sql statements select stdistance point sttransformstgeomfromtext point96 876369 29 905320 4326 32140 from distappsouthtexascity mdistances 147075 069813 139630 198056 140888 552826 138809 684197 158309 246259 212183 594374 70870 188967 165337 758878 139196 085105 select stdistance point sttransformstgeomfromtext point96 876369 29 905320 4326 2278 from distappsouthtexascityft ftdistances 482528 79154625 458103 408123001 462231 860397575 455411 438904354 519386 252102563 696139 009211594 232513 278304279 542445 630586414 456679 155883207 testing using different variations of parameters and using models with different projected coordinate systems dist1 southtexascity objects annotate distancedistancepoint lagrange orderbyid dist2 southtexascityft objects annotate distancedistancepoint lagrange orderbyid distqs dist1 dist2 ensuring expected distances are returned for each distance queryset for qs in distqs for i c in enumerateqs with self subtestcc self assertalmostequalmdistancesi c distance m 1 self assertalmostequalftdistancesi c distance surveyft 1 skipunlessdbfeaturehasdistancefunction supportsdistancegeodetic def testdistancegeodeticself testing geodetic distance calculation with a nonpoint geometry a linestring of wollongong and shellharbour coords ls linestring150 902 34 4245 150 87 34 5789 srid4326 reference query select stdistancesphere point stgeomfromtext linestring150 9020 34 4245 150 8700 34 5789 4326 from distappaustraliacity order by name distances 1120954 92533513 140575 720018241 640396 662906304 60580 9693849269 972807 955955075 568451 8357838 40435 4335201384 0 68272 3896586844 12375 0643697706 0 qs australiacity objects annotatedistancedistancepoint ls orderby name for city distance in zipqs distances with self subtestcitycity distancedistance testing equivalence to within a meter kilometer on spatialite tol 3 if connection ops spatialite else 0 self assertalmostequaldistance city distance m tol skipunlessdbfeaturehasdistancefunction supportsdistancegeodetic def testdistancegeodeticspheroidself tol 2 if connection ops oracle else 4 got the reference distances using the raw sql statements select stdistancespheroid point stgeomfromtext point151 231341 33 952685 4326 spheroidwgs 84 6378137 0 298 257223563 from distappaustraliacity where not id 11 select stdistancesphere point stgeomfromtext point151 231341 33 952685 4326 from distappaustraliacity where not id 11 stdistancesphere spheroiddistances 60504 0628957201 77023 9489850262 49154 8867574404 90847 4358768573 217402 811919332 709599 234564757 640011 483550888 7772 00667991925 1047861 78619339 1165126 55236034 spheredistances 60580 9693849267 77144 0435286473 49199 4415344719 90804 7533823494 217713 384600405 709134 127242793 639828 157159169 7786 82949717788 1049204 06569028 1162623 7238134 testing with spheroid distances first hillsdale australiacity objects getnamehillsdale qs australiacity objects excludeidhillsdale id annotatedistancedistancepoint hillsdale point spheroidtrue orderbyid for i c in enumerateqs with self subtestcc self assertalmostequalspheroiddistancesi c distance m tol if connection ops postgis or connection ops spatialite postgis uses sphereonly distances by default testing these as well qs australiacity objects excludeidhillsdale id annotatedistancedistancepoint hillsdale point orderbyid for i c in enumerateqs with self subtestcc self assertalmostequalspheredistancesi c distance m tol skipifdbfeaturesupportsdistancegeodetic skipunlessdbfeaturehasdistancefunction def testdistancefunctionrawresultself distance interstate objects annotate ddistancepoint0 0 srid4326 point0 1 srid4326 first d self assertequaldistance 1 skipunlessdbfeaturehasdistancefunction def testdistancefunctiondlookupself qs interstate objects annotate ddistancepoint0 0 srid3857 point0 1 srid3857 filterddm1 self asserttrueqs exists skipunlessdbfeaturesupportstoleranceparameter def testdistancefunctiontoleranceescapingself qs interstate objects annotate ddistance point500 500 srid3857 point0 0 srid3857 tolerance0 05 1 or 11 or 11 filterddm1 valuespk msg the tolerance parameter has the wrong type with self assertraisesmessagetypeerror msg qs exists skipunlessdbfeaturesupportstoleranceparameter def testdistancefunctiontoleranceself tolerance is greater than distance qs interstate objects annotate ddistance point0 0 srid3857 point1 1 srid3857 tolerance1 5 filterd0 valuespk self assertisqs exists true skipifdbfeaturesupportsdistancegeodetic skipunlessdbfeaturehasdistancefunction def testdistancefunctionrawresultdlookupself qs interstate objects annotate ddistancepoint0 0 srid4326 point0 1 srid4326 filterddm1 msg distance measure is supplied but units are unknown for result with self assertraisesmessagevalueerror msg listqs skipunlessdbfeaturehasdistancefunction hastransformfunction def testdistancetransformself we ll be using a polygon buffering the centroid of 77005 to 100m which aren t allowed in geographic distance queries normally however our field has been transformed to a nongeographic system z southtexaszipcode objects getname77005 reference query select stdistancesttransformdistappcensuszipcode poly 32140 stgeomfromtext bufferwkt 32140 from distappcensuszipcode distsm 3553 30384972258 1243 18391525602 2186 15439472242 having our buffer in the srid of the transformation and of the field should get the same results the first buffer has no need for transformation sql because it is the same srid as what was given to transform the second buffer will need to be transformed however buf1 z poly centroid buffer100 buf2 buf1 transform4269 clonetrue refzips 77002 77025 77401 for buf in buf1 buf2 qs censuszipcode objects excludename77005 annotatedistancedistancetransformpoly 32140 buf orderbyname self assertequalrefzips sortedc name for c in qs for i z in enumerateqs self assertalmostequalz distance m distsmi 5 skipunlessdbfeaturehasdistancefunction def testdistanceorderbyself qs southtexascity objects annotate distancedistancepoint point3 3 srid32140 orderbydistance valueslistname flattrue filternameinsan antonio pearland self assertsequenceequalqs san antonio pearland skipunlessdbfeaturehaslengthfunction def testlengthself reference query should use lengthspheroid select stlengthspheroid stgeomfromtext wkt 4326 spheroidwgs 84 6378137 298 257223563 ityepsg 7030 lenm1 473504 769553813 lenm2 4617 668 if connection features supportslengthgeodetic qs interstate objects annotatelengthlengthpath tol 2 if connection ops oracle else 3 self assertalmostequallenm1 qs0 length m tol todo test with spheroid argument true and false else does not support geodetic coordinate systems with self assertraisesnotsupportederror listinterstate objects annotatelengthlengthpath now doing length on a projected coordinate system i10 southtexasinterstate objects annotatelengthlengthpath get namei10 self assertalmostequallenm2 i10 length m 2 self asserttrue southtexasinterstate objects annotatelengthlengthpath filterlengthgt4000 exists length with an explicit geometry value qs interstate objects annotatelengthlengthi10 path self assertalmostequalqs first length m lenm2 2 skipunlessdbfeaturehasperimeterfunction def testperimeterself reference query select stperimeterdistappsouthtexaszipcode poly from distappsouthtexaszipcode perimm 18404 3550889361 15627 2108551001 20632 5588368978 17094 5996143697 tol 2 if connection ops oracle else 7 qs southtexaszipcode objects annotateperimeterperimeterpoly orderby name for i z in enumerateqs self assertalmostequalperimmi z perimeter m tol running on points should return 0 qs southtexascity objects annotateperimperimeterpoint for city in qs self assertequal0 city perim m skipunlessdbfeaturehasperimeterfunction def testperimetergeodeticself currently only oracle supports calculating the perimeter on geodetic geometries without being transformed qs1 censuszipcode objects annotateperimperimeterpoly if connection features supportsperimetergeodetic self assertalmostequalqs10 perim m 18406 3818954314 3 else with self assertraisesnotsupportederror listqs1 but should work fine when transformed to projected coordinates qs2 censuszipcode objects annotate perimperimetertransformpoly 32140 filtername77002 self assertalmostequalqs20 perim m 18404 355 3 skipunlessdbfeature supportsnullgeometries hasareafunction hasdistancefunction def testmeasurementnullfieldsself creating southtexaszipcode wnull value southtexaszipcode objects createname78212 performing distancearea queries against the null polygonfield and ensuring the result of the operations is none htown southtexascity objects getnamedowntown houston z southtexaszipcode objects annotate distancedistancepoly htown point areaareapoly getname78212 self assertisnonez distance self assertisnonez area alias for distance a point we are testing distances with using a wgs84 coordinate that ll be implicitly transformed to that to the coordinate system of the field epsg 32140 texas south central w units in meters another one for australia test initialization of distance models test the dwithin lookup type distances all should be equal except for the degree meter pair in au_cities that s somewhat approximate expected cities for australia and texas performing distance queries on two projected coordinate systems one with units in meters and the other in units of u s survey feet with a complex geometry expression now performing the dwithin queries on a geodetic coordinate system result in meters result in units of the field creating the query set a valueerror should be raised on postgis when trying to pass distance objects into a dwithin query using a geodetic field retrieving the cities within a 20km donut w a 7km radius hole thus houston and southside place will be excluded as tested in the test02_dwithin above doing a distance query using polygons instead of a point if we add a little more distance 77002 should be included test distance lookups on geodetic coordinate systems line is from canberra to sydney query is for all other cities within a 100km of that line which should exclude only hobart adelaide spatialite is less accurate and returns 102 8km for batemans bay too many params too few params getting all cities w in 550 miles of hobart cities that are either really close or really far from wollongong and using different units of distance yards 17km nautical miles normal geodetic distance lookup uses distance_sphere on postgis geodetic distance lookup but telling geodjango to use distance_spheroid instead we should get the same results b c accuracy variance won t matter in this test case with a combined expression with spheroid param with a complex geometry expression dwithin lookup in a subquery using outerref as a parameter linestring of wollongong and adelaide coords distance functions on postgis projected geometry lon lat geometry geography 4326 st_distance geom1 geom2 ok meters degrees ok meters st_distance geom1 geom2 use_spheroid false n a n a ok meters less accurate quick distance_sphere geom1 geom2 n a ok meters n a distance_spheroid geom1 geom2 spheroid n a ok meters n a st_perimeter geom1 ok degrees ok distance functions on spatialite projected geometry lon lat geometry st_distance geom1 geom2 ok meters n a st_distance geom1 geom2 use_ellipsoid true n a ok meters st_distance geom1 geom2 use_ellipsoid false n a ok meters less accurate quick perimeter geom1 ok degrees noqa reference queries select st_area poly from distapp_southtexaszipcode tolerance has to be lower for oracle test a simple distance query with projected coordinates and without transformation test the distance function on projected coordinate systems the point for la grange tx reference distances in feet and in meters got these values from using the provided raw sql statements select st_distance point st_transform st_geomfromtext point 96 876369 29 905320 4326 32140 from distapp_southtexascity select st_distance point st_transform st_geomfromtext point 96 876369 29 905320 4326 2278 from distapp_southtexascityft testing using different variations of parameters and using models with different projected coordinate systems ensuring expected distances are returned for each distance queryset test the distance function on geodetic coordinate systems testing geodetic distance calculation with a non point geometry a linestring of wollongong and shellharbour coords reference query select st_distance_sphere point st_geomfromtext linestring 150 9020 34 4245 150 8700 34 5789 4326 from distapp_australiacity order by name testing equivalence to within a meter kilometer on spatialite got the reference distances using the raw sql statements select st_distance_spheroid point st_geomfromtext point 151 231341 33 952685 4326 spheroid wgs 84 6378137 0 298 257223563 from distapp_australiacity where not id 11 select st_distance_sphere point st_geomfromtext point 151 231341 33 952685 4326 from distapp_australiacity where not id 11 st_distance_sphere testing with spheroid distances first postgis uses sphere only distances by default testing these as well tolerance is greater than distance test the distance function used with transform on a geographic field we ll be using a polygon buffering the centroid of 77005 to 100m which aren t allowed in geographic distance queries normally however our field has been transformed to a non geographic system reference query select st_distance st_transform distapp_censuszipcode poly 32140 st_geomfromtext buffer_wkt 32140 from distapp_censuszipcode having our buffer in the srid of the transformation and of the field should get the same results the first buffer has no need for transformation sql because it is the same srid as what was given to transform the second buffer will need to be transformed however test the length function reference query should use length_spheroid select st_length_spheroid st_geomfromtext wkt 4326 spheroid wgs 84 6378137 298 257223563 ity epsg 7030 todo test with spheroid argument true and false does not support geodetic coordinate systems now doing length on a projected coordinate system length with an explicit geometry value test the perimeter function reference query select st_perimeter distapp_southtexaszipcode poly from distapp_southtexaszipcode running on points should return 0 currently only oracle supports calculating the perimeter on geodetic geometries without being transformed but should work fine when transformed to projected coordinates test the measurement functions on fields with null values creating southtexaszipcode w null value performing distance area queries against the null polygonfield and ensuring the result of the operations is none
from django.contrib.gis.db.models.functions import (
    Area,
    Distance,
    Length,
    Perimeter,
    Transform,
    Union,
)
from django.contrib.gis.geos import GEOSGeometry, LineString, Point
from django.contrib.gis.measure import D  
from django.db import NotSupportedError, connection
from django.db.models import (
    Case,
    Count,
    Exists,
    F,
    IntegerField,
    OuterRef,
    Q,
    Value,
    When,
)
from django.test import TestCase, skipIfDBFeature, skipUnlessDBFeature

from ..utils import FuncTestMixin
from .models import (
    AustraliaCity,
    CensusZipcode,
    Interstate,
    SouthTexasCity,
    SouthTexasCityFt,
    SouthTexasInterstate,
    SouthTexasZipcode,
)


class DistanceTest(TestCase):
    fixtures = ["initial"]

    def setUp(self):
        
        
        
        
        self.stx_pnt = GEOSGeometry(
            "POINT (-95.370401017314293 29.704867409475465)", 4326
        )
        
        self.au_pnt = GEOSGeometry("POINT (150.791 -34.4919)", 4326)

    def get_names(self, qs):
        cities = [c.name for c in qs]
        cities.sort()
        return cities

    def test_init(self):
        
        self.assertEqual(9, SouthTexasCity.objects.count())
        self.assertEqual(9, SouthTexasCityFt.objects.count())
        self.assertEqual(11, AustraliaCity.objects.count())
        self.assertEqual(4, SouthTexasZipcode.objects.count())
        self.assertEqual(4, CensusZipcode.objects.count())
        self.assertEqual(1, Interstate.objects.count())
        self.assertEqual(1, SouthTexasInterstate.objects.count())

    @skipUnlessDBFeature("supports_dwithin_lookup")
    def test_dwithin(self):
        
        
        
        
        tx_dists = [(7000, 22965.83), D(km=7), D(mi=4.349)]
        au_dists = [(0.5, 32000), D(km=32), D(mi=19.884)]

        
        tx_cities = ["Downtown Houston", "Southside Place"]
        au_cities = ["Mittagong", "Shellharbour", "Thirroul", "Wollongong"]

        
        
        for dist in tx_dists:
            if isinstance(dist, tuple):
                dist1, dist2 = dist
            else:
                dist1 = dist2 = dist
            qs1 = SouthTexasCity.objects.filter(point__dwithin=(self.stx_pnt, dist1))
            qs2 = SouthTexasCityFt.objects.filter(point__dwithin=(self.stx_pnt, dist2))
            for qs in qs1, qs2:
                with self.subTest(dist=dist, qs=qs):
                    self.assertEqual(tx_cities, self.get_names(qs))

        
        self.assertFalse(
            SouthTexasCity.objects.exclude(point__dwithin=(Union("point", "point"), 0))
        )

        
        for dist in au_dists:
            with self.subTest(dist=dist):
                type_error = isinstance(dist, D) and not connection.ops.oracle
                if isinstance(dist, tuple):
                    if connection.ops.oracle or connection.ops.spatialite:
                        
                        dist = dist[1]
                    else:
                        
                        dist = dist[0]

                
                qs = AustraliaCity.objects.order_by("name")
                if type_error:
                    
                    
                    
                    with self.assertRaises(ValueError):
                        AustraliaCity.objects.filter(
                            point__dwithin=(self.au_pnt, dist)
                        ).count()
                else:
                    self.assertEqual(
                        au_cities,
                        self.get_names(qs.filter(point__dwithin=(self.au_pnt, dist))),
                    )

    @skipUnlessDBFeature("supports_distances_lookups")
    def test_distance_lookups(self):
        
        
        
        for model in [SouthTexasCity, SouthTexasCityFt]:
            stx_pnt = self.stx_pnt.transform(
                model._meta.get_field("point").srid, clone=True
            )
            qs = model.objects.filter(point__distance_gte=(stx_pnt, D(km=7))).filter(
                point__distance_lte=(stx_pnt, D(km=20)),
            )
            cities = self.get_names(qs)
            self.assertEqual(cities, ["Bellaire", "Pearland", "West University Place"])

        
        z = SouthTexasZipcode.objects.get(name="77005")
        qs = SouthTexasZipcode.objects.exclude(name="77005").filter(
            poly__distance_lte=(z.poly, D(m=275))
        )
        self.assertEqual(["77025", "77401"], self.get_names(qs))
        
        qs = SouthTexasZipcode.objects.exclude(name="77005").filter(
            poly__distance_lte=(z.poly, D(m=300))
        )
        self.assertEqual(["77002", "77025", "77401"], self.get_names(qs))

    @skipUnlessDBFeature("supports_distances_lookups", "supports_distance_geodetic")
    def test_geodetic_distance_lookups(self):
        
        
        
        line = GEOSGeometry("LINESTRING(144.9630 -37.8143,151.2607 -33.8870)", 4326)
        dist_qs = AustraliaCity.objects.filter(point__distance_lte=(line, D(km=100)))
        expected_cities = [
            "Batemans Bay",
            "Canberra",
            "Hillsdale",
            "Melbourne",
            "Mittagong",
            "Shellharbour",
            "Sydney",
            "Thirroul",
            "Wollongong",
        ]
        if connection.ops.spatialite:
            
            expected_cities.pop(0)
        self.assertEqual(expected_cities, self.get_names(dist_qs))

        msg = "2, 3, or 4-element tuple required for 'distance_lte' lookup."
        with self.assertRaisesMessage(ValueError, msg):  
            len(
                AustraliaCity.objects.filter(
                    point__distance_lte=(
                        "POINT(5 23)",
                        D(km=100),
                        "spheroid",
                        "4",
                        None,
                    )
                )
            )

        with self.assertRaisesMessage(ValueError, msg):  
            len(AustraliaCity.objects.filter(point__distance_lte=("POINT(5 23)",)))

        msg = "For 4-element tuples the last argument must be the 'spheroid' directive."
        with self.assertRaisesMessage(ValueError, msg):
            len(
                AustraliaCity.objects.filter(
                    point__distance_lte=("POINT(5 23)", D(km=100), "spheroid", "4")
                )
            )

        
        hobart = AustraliaCity.objects.get(name="Hobart")
        qs = AustraliaCity.objects.exclude(name="Hobart").filter(
            point__distance_lte=(hobart.point, D(mi=550))
        )
        cities = self.get_names(qs)
        self.assertEqual(cities, ["Batemans Bay", "Canberra", "Melbourne"])

        
        
        wollongong = AustraliaCity.objects.get(name="Wollongong")
        d1, d2 = D(yd=19500), D(nm=400)  

        
        gq1 = Q(point__distance_lte=(wollongong.point, d1))
        gq2 = Q(point__distance_gte=(wollongong.point, d2))
        qs1 = AustraliaCity.objects.exclude(name="Wollongong").filter(gq1 | gq2)

        
        
        
        querysets = [qs1]
        if connection.features.has_DistanceSpheroid_function:
            gq3 = Q(point__distance_lte=(wollongong.point, d1, "spheroid"))
            gq4 = Q(point__distance_gte=(wollongong.point, d2, "spheroid"))
            qs2 = AustraliaCity.objects.exclude(name="Wollongong").filter(gq3 | gq4)
            querysets.append(qs2)

        for qs in querysets:
            cities = self.get_names(qs)
            self.assertEqual(cities, ["Adelaide", "Hobart", "Shellharbour", "Thirroul"])

    @skipUnlessDBFeature("supports_distances_lookups")
    def test_distance_lookups_with_expression_rhs(self):
        stx_pnt = self.stx_pnt.transform(
            SouthTexasCity._meta.get_field("point").srid, clone=True
        )
        qs = SouthTexasCity.objects.filter(
            point__distance_lte=(stx_pnt, F("radius")),
        ).order_by("name")
        self.assertEqual(
            self.get_names(qs),
            [
                "Bellaire",
                "Downtown Houston",
                "Southside Place",
                "West University Place",
            ],
        )

        
        qs = SouthTexasCity.objects.filter(
            point__distance_lte=(stx_pnt, F("radius") * 2),
        ).order_by("name")
        self.assertEqual(len(qs), 5)
        self.assertIn("Pearland", self.get_names(qs))

        
        if connection.features.supports_distance_geodetic:
            hobart = AustraliaCity.objects.get(name="Hobart")
            AustraliaCity.objects.update(ref_point=hobart.point)
            for ref_point in [hobart.point, F("ref_point")]:
                qs = AustraliaCity.objects.filter(
                    point__distance_lte=(ref_point, F("radius") * 70, "spheroid"),
                ).order_by("name")
                self.assertEqual(
                    self.get_names(qs), ["Canberra", "Hobart", "Melbourne"]
                )

        
        self.assertFalse(
            SouthTexasCity.objects.filter(
                point__distance_gt=(Union("point", "point"), 0)
            )
        )
        self.assertEqual(
            SouthTexasCity.objects.filter(
                point__distance_lte=(Union("point", "point"), 0)
            ).count(),
            SouthTexasCity.objects.count(),
        )

    @skipUnlessDBFeature("supports_distances_lookups")
    def test_distance_annotation_group_by(self):
        stx_pnt = self.stx_pnt.transform(
            SouthTexasCity._meta.get_field("point").srid,
            clone=True,
        )
        qs = (
            SouthTexasCity.objects.annotate(
                relative_distance=Case(
                    When(point__distance_lte=(stx_pnt, D(km=20)), then=Value(20)),
                    default=Value(100),
                    output_field=IntegerField(),
                ),
            )
            .values("relative_distance")
            .annotate(count=Count("pk"))
        )
        self.assertCountEqual(
            qs,
            [
                {"relative_distance": 20, "count": 5},
                {"relative_distance": 100, "count": 4},
            ],
        )

    def test_mysql_geodetic_distance_error(self):
        if not connection.ops.mysql:
            self.skipTest("This is a MySQL-specific test.")
        msg = (
            "Only numeric values of degree units are allowed on geodetic distance "
            "queries."
        )
        with self.assertRaisesMessage(ValueError, msg):
            AustraliaCity.objects.filter(
                point__distance_lte=(Point(0, 0), D(m=100))
            ).exists()

    @skipUnlessDBFeature("supports_dwithin_lookup")
    def test_dwithin_subquery(self):
        
        qs = CensusZipcode.objects.annotate(
            annotated_value=Exists(
                SouthTexasCity.objects.filter(
                    point__dwithin=(OuterRef("poly"), D(m=10)),
                )
            )
        ).filter(annotated_value=True)
        self.assertEqual(self.get_names(qs), ["77002", "77025", "77401"])

    @skipUnlessDBFeature("supports_dwithin_lookup", "supports_dwithin_distance_expr")
    def test_dwithin_with_expression_rhs(self):
        
        ls = LineString(((150.902, -34.4245), (138.6, -34.9258)), srid=4326)
        qs = AustraliaCity.objects.filter(
            point__dwithin=(ls, F("allowed_distance")),
        ).order_by("name")
        self.assertEqual(
            self.get_names(qs),
            ["Adelaide", "Mittagong", "Shellharbour", "Thirroul", "Wollongong"],
        )

    @skipIfDBFeature("supports_dwithin_distance_expr")
    def test_dwithin_with_expression_rhs_not_supported(self):
        ls = LineString(((150.902, -34.4245), (138.6, -34.9258)), srid=4326)
        msg = (
            "This backend does not support expressions for specifying "
            "distance in the dwithin lookup."
        )
        with self.assertRaisesMessage(NotSupportedError, msg):
            list(
                AustraliaCity.objects.filter(
                    point__dwithin=(ls, F("allowed_distance")),
                )
            )


  


class DistanceFunctionsTests(FuncTestMixin, TestCase):
    fixtures = ["initial"]

    @skipUnlessDBFeature("has_Area_function")
    def test_area(self):
        
        
        area_sq_m = [
            5437908.90234375,
            10183031.4389648,
            11254471.0073242,
            9881708.91772461,
        ]
        
        tol = 2
        for i, z in enumerate(
            SouthTexasZipcode.objects.annotate(area=Area("poly")).order_by("name")
        ):
            self.assertAlmostEqual(area_sq_m[i], z.area.sq_m, tol)

    @skipUnlessDBFeature("has_Distance_function")
    def test_distance_simple(self):
        
        lagrange = GEOSGeometry("POINT(805066.295722839 4231496.29461335)", 32140)
        houston = (
            SouthTexasCity.objects.annotate(dist=Distance("point", lagrange))
            .order_by("id")
            .first()
        )
        tol = 2 if connection.ops.oracle else 5
        self.assertAlmostEqual(houston.dist.m, 147075.069813, tol)

    @skipUnlessDBFeature("has_Distance_function", "has_Transform_function")
    def test_distance_projected(self):
        
        
        lagrange = GEOSGeometry("POINT(-96.876369 29.905320)", 4326)
        
        
        
        
        
        
        
        m_distances = [
            147075.069813,
            139630.198056,
            140888.552826,
            138809.684197,
            158309.246259,
            212183.594374,
            70870.188967,
            165337.758878,
            139196.085105,
        ]
        
        
        
        
        
        ft_distances = [
            482528.79154625,
            458103.408123001,
            462231.860397575,
            455411.438904354,
            519386.252102563,
            696139.009211594,
            232513.278304279,
            542445.630586414,
            456679.155883207,
        ]

        
        
        dist1 = SouthTexasCity.objects.annotate(
            distance=Distance("point", lagrange)
        ).order_by("id")
        dist2 = SouthTexasCityFt.objects.annotate(
            distance=Distance("point", lagrange)
        ).order_by("id")
        dist_qs = [dist1, dist2]

        
        for qs in dist_qs:
            for i, c in enumerate(qs):
                with self.subTest(c=c):
                    self.assertAlmostEqual(m_distances[i], c.distance.m, -1)
                    self.assertAlmostEqual(ft_distances[i], c.distance.survey_ft, -1)

    @skipUnlessDBFeature("has_Distance_function", "supports_distance_geodetic")
    def test_distance_geodetic(self):
        
        
        
        ls = LineString(((150.902, -34.4245), (150.87, -34.5789)), srid=4326)

        
        
        
        
        
        
        distances = [
            1120954.92533513,
            140575.720018241,
            640396.662906304,
            60580.9693849269,
            972807.955955075,
            568451.8357838,
            40435.4335201384,
            0,
            68272.3896586844,
            12375.0643697706,
            0,
        ]
        qs = AustraliaCity.objects.annotate(distance=Distance("point", ls)).order_by(
            "name"
        )
        for city, distance in zip(qs, distances):
            with self.subTest(city=city, distance=distance):
                
                tol = -3 if connection.ops.spatialite else 0
                self.assertAlmostEqual(distance, city.distance.m, tol)

    @skipUnlessDBFeature("has_Distance_function", "supports_distance_geodetic")
    def test_distance_geodetic_spheroid(self):
        tol = 2 if connection.ops.oracle else 4

        
        
        
        
        
        
        
        
        
        
        
        
        spheroid_distances = [
            60504.0628957201,
            77023.9489850262,
            49154.8867574404,
            90847.4358768573,
            217402.811919332,
            709599.234564757,
            640011.483550888,
            7772.00667991925,
            1047861.78619339,
            1165126.55236034,
        ]
        sphere_distances = [
            60580.9693849267,
            77144.0435286473,
            49199.4415344719,
            90804.7533823494,
            217713.384600405,
            709134.127242793,
            639828.157159169,
            7786.82949717788,
            1049204.06569028,
            1162623.7238134,
        ]
        
        hillsdale = AustraliaCity.objects.get(name="Hillsdale")
        qs = (
            AustraliaCity.objects.exclude(id=hillsdale.id)
            .annotate(distance=Distance("point", hillsdale.point, spheroid=True))
            .order_by("id")
        )
        for i, c in enumerate(qs):
            with self.subTest(c=c):
                self.assertAlmostEqual(spheroid_distances[i], c.distance.m, tol)
        if connection.ops.postgis or connection.ops.spatialite:
            
            qs = (
                AustraliaCity.objects.exclude(id=hillsdale.id)
                .annotate(distance=Distance("point", hillsdale.point))
                .order_by("id")
            )
            for i, c in enumerate(qs):
                with self.subTest(c=c):
                    self.assertAlmostEqual(sphere_distances[i], c.distance.m, tol)

    @skipIfDBFeature("supports_distance_geodetic")
    @skipUnlessDBFeature("has_Distance_function")
    def test_distance_function_raw_result(self):
        distance = (
            Interstate.objects.annotate(
                d=Distance(Point(0, 0, srid=4326), Point(0, 1, srid=4326)),
            )
            .first()
            .d
        )
        self.assertEqual(distance, 1)

    @skipUnlessDBFeature("has_Distance_function")
    def test_distance_function_d_lookup(self):
        qs = Interstate.objects.annotate(
            d=Distance(Point(0, 0, srid=3857), Point(0, 1, srid=3857)),
        ).filter(d=D(m=1))
        self.assertTrue(qs.exists())

    @skipUnlessDBFeature("supports_tolerance_parameter")
    def test_distance_function_tolerance_escaping(self):
        qs = (
            Interstate.objects.annotate(
                d=Distance(
                    Point(500, 500, srid=3857),
                    Point(0, 0, srid=3857),
                    tolerance="0.05) = 1 OR 1=1 OR (1+1",
                ),
            )
            .filter(d=D(m=1))
            .values("pk")
        )
        msg = "The tolerance parameter has the wrong type"
        with self.assertRaisesMessage(TypeError, msg):
            qs.exists()

    @skipUnlessDBFeature("supports_tolerance_parameter")
    def test_distance_function_tolerance(self):
        
        qs = (
            Interstate.objects.annotate(
                d=Distance(
                    Point(0, 0, srid=3857),
                    Point(1, 1, srid=3857),
                    tolerance=1.5,
                ),
            )
            .filter(d=0)
            .values("pk")
        )
        self.assertIs(qs.exists(), True)

    @skipIfDBFeature("supports_distance_geodetic")
    @skipUnlessDBFeature("has_Distance_function")
    def test_distance_function_raw_result_d_lookup(self):
        qs = Interstate.objects.annotate(
            d=Distance(Point(0, 0, srid=4326), Point(0, 1, srid=4326)),
        ).filter(d=D(m=1))
        msg = "Distance measure is supplied, but units are unknown for result."
        with self.assertRaisesMessage(ValueError, msg):
            list(qs)

    @skipUnlessDBFeature("has_Distance_function", "has_Transform_function")
    def test_distance_transform(self):
        
        
        
        
        
        z = SouthTexasZipcode.objects.get(name="77005")

        
        
        
        
        dists_m = [3553.30384972258, 1243.18391525602, 2186.15439472242]

        
        
        
        
        
        buf1 = z.poly.centroid.buffer(100)
        buf2 = buf1.transform(4269, clone=True)
        ref_zips = ["77002", "77025", "77401"]

        for buf in [buf1, buf2]:
            qs = (
                CensusZipcode.objects.exclude(name="77005")
                .annotate(distance=Distance(Transform("poly", 32140), buf))
                .order_by("name")
            )
            self.assertEqual(ref_zips, sorted(c.name for c in qs))
            for i, z in enumerate(qs):
                self.assertAlmostEqual(z.distance.m, dists_m[i], 5)

    @skipUnlessDBFeature("has_Distance_function")
    def test_distance_order_by(self):
        qs = (
            SouthTexasCity.objects.annotate(
                distance=Distance("point", Point(3, 3, srid=32140))
            )
            .order_by("distance")
            .values_list("name", flat=True)
            .filter(name__in=("San Antonio", "Pearland"))
        )
        self.assertSequenceEqual(qs, ["San Antonio", "Pearland"])

    @skipUnlessDBFeature("has_Length_function")
    def test_length(self):
        
        
        
        
        
        
        len_m1 = 473504.769553813
        len_m2 = 4617.668

        if connection.features.supports_length_geodetic:
            qs = Interstate.objects.annotate(length=Length("path"))
            tol = 2 if connection.ops.oracle else 3
            self.assertAlmostEqual(len_m1, qs[0].length.m, tol)
            
        else:
            
            with self.assertRaises(NotSupportedError):
                list(Interstate.objects.annotate(length=Length("path")))

        
        i10 = SouthTexasInterstate.objects.annotate(length=Length("path")).get(
            name="I-10"
        )
        self.assertAlmostEqual(len_m2, i10.length.m, 2)
        self.assertTrue(
            SouthTexasInterstate.objects.annotate(length=Length("path"))
            .filter(length__gt=4000)
            .exists()
        )
        
        qs = Interstate.objects.annotate(length=Length(i10.path))
        self.assertAlmostEqual(qs.first().length.m, len_m2, 2)

    @skipUnlessDBFeature("has_Perimeter_function")
    def test_perimeter(self):
        
        
        
        
        perim_m = [
            18404.3550889361,
            15627.2108551001,
            20632.5588368978,
            17094.5996143697,
        ]
        tol = 2 if connection.ops.oracle else 7
        qs = SouthTexasZipcode.objects.annotate(perimeter=Perimeter("poly")).order_by(
            "name"
        )
        for i, z in enumerate(qs):
            self.assertAlmostEqual(perim_m[i], z.perimeter.m, tol)

        
        qs = SouthTexasCity.objects.annotate(perim=Perimeter("point"))
        for city in qs:
            self.assertEqual(0, city.perim.m)

    @skipUnlessDBFeature("has_Perimeter_function")
    def test_perimeter_geodetic(self):
        
        
        qs1 = CensusZipcode.objects.annotate(perim=Perimeter("poly"))
        if connection.features.supports_perimeter_geodetic:
            self.assertAlmostEqual(qs1[0].perim.m, 18406.3818954314, 3)
        else:
            with self.assertRaises(NotSupportedError):
                list(qs1)
        
        qs2 = CensusZipcode.objects.annotate(
            perim=Perimeter(Transform("poly", 32140))
        ).filter(name="77002")
        self.assertAlmostEqual(qs2[0].perim.m, 18404.355, 3)

    @skipUnlessDBFeature(
        "supports_null_geometries", "has_Area_function", "has_Distance_function"
    )
    def test_measurement_null_fields(self):
        
        
        SouthTexasZipcode.objects.create(name="78212")
        
        
        htown = SouthTexasCity.objects.get(name="Downtown Houston")
        z = SouthTexasZipcode.objects.annotate(
            distance=Distance("poly", htown.point), area=Area("poly")
        ).get(name="78212")
        self.assertIsNone(z.distance)
        self.assertIsNone(z.area)