0

SQL within that zone is a point

SELECT
    "public".zona.zts
FROM
    "public".zona
WHERE
    ST_Within (
        ST_Transform (
            ST_GeomFromText (
                'POINT(-30.033514 -51.226828)',
                4326
            ),
            3857
        ),
        zona.geom
    );

I tested in a single step and in separate steps. But the result is always no record. This pair of coordinates of the point I'm getting via google maps api in a single page geocode addresses, this is my geocode:

<!DOCTYPE html>
<html>
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
    <meta charset="utf-8">
    <title>Google Maps JavaScript API v3 Example: Geocoding Simple</title>
    <link href="https://developers.google.com/maps/documentation/javascript/examples/default.css" rel="stylesheet">

    <script src="http://maps.google.com/maps/api/js?sensor=false&v=3.2"></script> 


    <script>
      var geocoder;
      var map;
      var mapOptions = {
          zoom: 17,
          mapTypeId: google.maps.MapTypeId.ROADMAP
        }
      var marker;

      function initialize() {
        geocoder = new google.maps.Geocoder();
        map = new google.maps.Map(document.getElementById('map_canvas'), mapOptions);
        codeAddress();
      }

      function codeAddress() {
        var address = document.getElementById('address').value;
        geocoder.geocode( { 'address': address}, function(results, status) {
          if (status == google.maps.GeocoderStatus.OK) {
            map.setCenter(results[0].geometry.location);
            if(marker)
              marker.setMap(null);
            marker = new google.maps.Marker({
                map: map,
                position: results[0].geometry.location,
                draggable: true
            });
            google.maps.event.addListener(marker, "dragend", function() {
              document.getElementById('lat').value = marker.getPosition().lat();
              document.getElementById('lng').value = marker.getPosition().lng();
            });
            document.getElementById('lat').value = marker.getPosition().lat();
            document.getElementById('lng').value = marker.getPosition().lng();
          } else {
            alert('Geocode was not successful for the following reason: ' + status);
          }
        });
      }
    </script>
  </head>
  <body onload="initialize()">
    <div>
      <input id="address" type="textbox" style="width:60%" value="Rua Joao Neves da Fontoura,7-Porto Alegre">
      <input type="button" value="Geocode" onclick="codeAddress()">
      <input type="text" id="lat"/>
      <input type="text" id="lng"/>

    </div>
    <div id="map_canvas" style="height:60%;top:30px"></div>
  </body>
</html>

She gives me coordinates in decimal degrees and my postgis table is in UTM EPSG: 3857 how can I do this conversion? The way I did in the above sql does not work. Out of curiosity I have sinned a point of a polygon that forms a zone and put as parâmentro and it worked.

7
  • misspelled - should be: ST_Within()
    – mtn.biker
    Jun 9, 2014 at 18:15
  • Corrected the error but returns no value and made ​​sure that this point is inside a zone. What can be causing this problem? My table areas are in EPSG: 3857 and the point I turned to ESPG: 3857 too.
    – csf
    Jun 9, 2014 at 18:26
  • You've forgotten to assign the coordinate system to the point. You can do it all in one step, like this: SELECT "public".zona.zts FROM "public".zona WHERE ST_Within ( ST_Transform (ST_GeomFromText ('POINT(-30.033514 -51.226828)',4326),3857), zona.geom );
    – mtn.biker
    Jun 9, 2014 at 18:33
  • I edited the question it did not work my sql.
    – csf
    Jun 9, 2014 at 19:29
  • OK, let's simplify the sql statement and use ST_Intersects() instead of ST_Within(). Remember the order of your coordinates is 'POINT( latitude longitude )'. You need to specify columns to return from your query ( * ). This works for me: SELECT column_name FROM table_name WHERE ST_Intersects( ST_GeomFromText('POINT(35 -75)',4326), geom_column_name) LIMIT 1;
    – mtn.biker
    Jun 9, 2014 at 20:02

3 Answers 3

1

Building on Kevin's answer - except WKT point coordinates are separated by a space, not a comma:

SELECT ST_AsText (ST_Transform (ST_GeomFromText ('POINT(-30.033514 -51.226828)',4326),3857)) AS wgs_geom;
2
  • Ha thanks @mtn.biker! I just noticed that myself. Added as an edit to my answer above. Jun 9, 2014 at 17:46
  • Thanks! I edited the question about the function ST_Within I need to consult within that region is this point that I created ... can you help me?
    – csf
    Jun 9, 2014 at 18:07
0

instead of POINT((-30.033514, -51.226828)), try POINT(-30.033514 -51.226828). WKT for points doesn't need a double set of parentheses, and shouldn't have commas between values. See wikipedia entry for WKT for more info.

0

SELECT ST_AsText ( ST_Transform ( ST_GeomFromText ( 'POINT(-30.033514 -51.226828)', 4326 ), 3857 ) ) AS wgs_geom

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.