2

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions?

The code is the first carid example.

UPDATE car_check 
set fencing=ST_Contains
from (
SELECT ST_Contains(ST_AsText('01030000..'),
'POINT(23.912784 120.99178)') 
)as foo
where carid='AD-5487' and cargroupid='1'

enter image description here

4
  • 1
    Could you tell what do you want to achieve, what is not working?
    – Mentor
    Feb 23, 2014 at 12:25
  • To update all car_check table field fencing,the picture is my first sql
    – Alan Yu
    Feb 23, 2014 at 14:11
  • Do you get an error or something? Feb 23, 2014 at 21:07
  • The code is correct.But it just can write the first row on fencing field.I want to do the code which can write the all rows
    – Alan Yu
    Feb 24, 2014 at 4:49

2 Answers 2

3

Try this untangled version to update all rows of char_check:

UPDATE car_check c
SET    fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8))
FROM   test t
      ,rule r
JOIN   (
   SELECT DISTINCT ON (polyname)
          polyname, geom
   FROM   unknown_table
   ORDER  BY polyname, version DESC
    ) u USING (polyname)
WHERE  t.id = c.carid
AND    r.cargroupid = c.cargroupid

Major points

  • Use the simpler ST_MakePoint to construct a point geometry from numeric input.

  • You do not need ST_AsText() at all, since your column geom is of type geometry already.

  • Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
    How do I efficiently get "the most recent corresponding row"?

  • In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)

2
  • Wow,it's very helpful to me.
    – Alan Yu
    Mar 8, 2014 at 14:57
  • t.log::float8, t.lat::float8 have to upside down
    – Alan Yu
    Mar 8, 2014 at 14:58
2

You don't seem to join the tables you are trying to update from. I think the query will need to be converted to this:

UPDATE car_check 
set fencing=foo.ST_Contains
from (
SELECT ST_Contains(ST_AsText(p.geom),
ST_GeomFromText('POINT(' || to_char(t.lat, '9999.999999') || ' ' || to_char(t.log, '9999.999999') || ')') AS ST_Contains, cc.carid, cc.cargroupid
FROM test t INNER JOIN car_check cc ON t.id = cc.carid
INNER JOIN rule r ON cc.cargroupid = r.cargroupid
INNER JOIN poly p ON r.polyname = p.polyname
WHERE p.poly_version = (SELECT MAX(p1.poly_version) FROM poly p1 WHERE p1.polyname = p.polyname))as foo 
INNER JOIN car_check 
ON foo.carid = car_check.carid AND foo.cargroupid = car_check.cargroupid
where carid='AD-5487' and cargroupid='1'

(I have no idea how your last table is called. The name is cut off from the picture. I called it poly in my statement. Fix it in yours)

7
  • parse error - invalid geometry LINE 5: 'POINT(t.lat, t.log)') AS ST_Contains, cc.carid, cc.cargroup... ^ HINT: "POINT(t." <-- parse error at position 8 within geometry
    – Alan Yu
    Feb 24, 2014 at 5:49
  • where carid='AD-5487' and cargroupid='1' , I want it to update to all rows.
    – Alan Yu
    Feb 24, 2014 at 5:51
  • see my updated answer. For some reason I left the single quotes from your original query. They do not need to be there
    – cha
    Feb 24, 2014 at 5:51
  • go ahead and remove the last where clause. I left it there, as I thought you are interested in those records only
    – cha
    Feb 24, 2014 at 5:53
  • try this one. BTW, it will help if you could provide a SQL Fiddle with a sample data.
    – cha
    Feb 24, 2014 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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