2 Replies Latest reply: Aug 7, 2013 4:20 PM by User581867-Oracle RSS

    Issue with Geometry  (0,0) coordinates

    User581867-Oracle
      Hello,
      When performing job search in i Recruitment found that all jobs are not retried when performing geocode search using Distance from Location functionality.

      Narrowed down and found few addresses have Geometry coordinates (0,0) which are posing this problem.
      Example :

      Geometry :
      MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)


      Address line 1 :1560 Regent Ave
      Town/City :Winnipeg
      Region : MB
      Postal code:R2C 3B4
      COuntry : CA

      This customer has 200 such Canada Addresses with (0,0) geometry details.

      Can you please advise how to get the Geometry colun populated with proper coordinates so that the searches can be successfull.

      Thanks,
      Gopi
        • 1. Re: Issue with Geometry  (0,0) coordinates
          Stefan Jager
          Hi Gopi,

          1. Find the proper coordinates for those addresses (either by Geocoding yourself, or using a third party service - I'm using alittle javascript to get coordinates from Google Maps for the occasional address - or from the original supplier, or ...)
          2. Update the Geometry with those coordinates.

          I'll assume you have a table called ADDRESS which has some sort of unique identifier, called ID, and a geometry column called GEOMETRY:

          Find which ID's have 0,0 as location:
          select adr.id 
          from address adr 
          where adr.geometry.sdo_point.x = 0 and 
                adr.geometry.sdo_point.y = 0;
          That'll give you the ID's. Updating those points could be done with something like this:
          update address adr
          set adr.geometry = MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(123456.12,123456.12,NULL),NULL,NULL)
          where adr.ID=1;
          Modify the above as fitting your situation and data, and it should be ok. If you used Oracle' geocoding to create these points in the first place, you should be able to use that to do the update. If you explain a little bit more about your data, we can tweak the above SQL to do it in one go possibly.

          HTH,
          Stefan
          • 2. Re: Issue with Geometry  (0,0) coordinates
            User581867-Oracle

            Hello,

                        Here is the script I modified to collect the details :

             

             

             

            select adr.location_id,adr.location_code,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,COUNTRY,POSTAL_CODE,STYLE

            from  hr_locations_all adr 

            where adr.geometry.sdo_point.x = 0 and 

            adr.geometry.sdo_point.y = 0;

             

             

            Copied  4 of the 35 records having problem with:

            Thanks,

            Gopi

            LOCATION_IDLOCATION_CODEADDRESS_LINE_1ADDRESS_LINE_2ADDRESS_LINE_3TOWN_OR_CITYCOUNTRYPOSTAL_CODESTYLE
            361Ontario PMED8th Floor, Ferguson Block77 Wellesley Street West TorontoCAM7A 1N3CA
            371WCB Ontario200 Front Street West TorontoCAM5V 3J1CA
            374WCB Saskatchewan200-1881 Scarth Street ReginaCAS4P 4L1CA
            9522006 - Edmonton AB8930-82nd Ave NW EdmontonCAT6C 0Z3

            CA