CODEWITHSUNDEEP
postgresqlgispostgis
shiva
shiva

Reputation: 13

POSTGIS - find whether given point(lon, lat) is within geography column (representation of polygon) of table

QN: Am getting error from ST_Within function for the geometry supplied in PostGIS. Not sure on what is the rite way to implement first argument of ST_Within.

Note: variables longitude and latitude are of character type supplied to function. However, I am not able to construct the value passed to ST_Within.

extract of code from custom function:

pointText := 'POINT(longitude ||   || latitude )';
result := (select p_code 
           from public.parking_lot 
           b where ST_Within (ST_GeomFromText(pointText)::geometry::geography,wkb_geography) limit 1);

EDIT: 14TH Nov 2019 With @jim-jones suggestion, I have re-created my function as follows:

CREATE OR REPLACE FUNCTION public.returnParkingLot(
    longitude character,
    latitude character)
  RETURNS character AS
$BODY$  
declare result text;
declare pointText text;
BEGIN
    IF longitude = '0' THEN
        return '';
    ELSE

        pointText := 'POINT(' || longitude || ' ' || latitude || ')';

        result := (select p_code from public.parking_lot b where ST_Within (ST_GeomFromText(pointText),b.wkb_geography::GEOMETRY) limit 1);
        return result;
    END IF;

END;$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION public.returnParkingLot(character, character)
  OWNER TO mother;

Access: I use the above function using select statement as follows:

select public.returnParkingLot('103.84472222','1.28333333');

Notes: Wish to know whether this implementation is right.

Edit: 21st Nov

As requested by @jim-jones, changes to datatype of inputs from user below.

CREATE OR REPLACE FUNCTION public.returnParkingLot(
    longitude double precision,
    latitude double precision)
  RETURNS character AS
$BODY$  
declare result text;
declare pointText text;
BEGIN
    IF longitude = '0' THEN
        return '';
    ELSE

        pointText := 'POINT(' || longitude || ' ' || latitude || ')';

        result := (select p_code from public.parking_lot b where ST_Within (ST_GeomFromText(pointText),b.wkb_geography::GEOMETRY) limit 1);
        return result;
    END IF;

END;$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION public.returnParkingLot(double precision, double precision)
  OWNER TO mother;

Select statement:

select public.returnParkingLot(103.84472222, 1.28333333);

Upvotes: 2

Views: 861

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

Considering that a) pointText contains valid numeric parameters and b) wkb_geography is indeed of type geography, we can assume that the problem is the parameters data type. According to the documentation ST_Within expects geometry. You might wanna try this out:

SELECT p_code 
FROM public.parking_lot b 
WHERE ST_Within(ST_GeomFromText('POINT(2 2)'),b.wkb_geography::GEOMETRY);

Your function should look like this:

CREATE OR REPLACE FUNCTION public.returnParkingLot(
  longitude NUMERIC,
  latitude NUMERIC) RETURNS character AS
$BODY$  
DECLARE result text;
DECLARE pointText text;
BEGIN
IF longitude = '0' THEN
  RETURN '';
ELSE
  pointText := 'POINT(' || longitude || ' ' || latitude || ')';
  result := (SELECT p_code FROM public.parking_lot b 
     WHERE ST_Within(ST_GeomFromText(pointText),b.wkb_geography::GEOMETRY) LIMIT 1);
  RETURN result;
END IF;
END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100;

Test:

CREATE TABLE parking_lot (p_code INT, wkb_geography GEOMETRY);
INSERT INTO parking_lot VALUES (1,'POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))');
SELECT public.returnParkingLot(30, 11);

 returnparkinglot 
------------------
 1
(1 Zeile)

Side note: Do you really need this much precision to locate a parking lot? With eight decimal places you're almost in the microscopy realm.

Upvotes: 1

Related Questions