PostGIS for fast prototyping and Research
I used extensively postgis (+ecosystem) for my phd thesis, in several ways. The first is that PostGIS is a good steady horse (elephant?): a database is the perfect place to store a lot of very different information in the same place and put them in relation. For geospatial data, postgis means you always have a way to put data in relation (are they at the same place?).
So we have a vehicle with lots of sensors (camera, lidar, gps, etc.) Postgis is the perfect candidate to store metadata of all of this. For instance I would store the image position, the lidar points using pgpointcloud, the trajectory, the metadata,etc. Postgis advanced linear referencing capabilities means its possible to deal with the temporal aspect of the data as well.
Second usage is PostGIS ecosystem. Postgis has a strong ecosystem, and that alone is a very good reason to use it. This ecosystem is even wider because so many tools can read/write into PostGIS. I mentioned pgpointcloud, but postgis_topology is the base of my work, and I also used pgrouting to perform graph analysis and road network reconstruction, and postgis raster to store and use some aerial images, as well as postgres extension (text analysis).
Third usage: power user Now for the advanced part, postgis is so reliable and flexible it can be abused to perform all sort of nice things, especially in the field of automatism. In my phd I perform road generation for Paris fully in the database, with a lot of calls to postgis geometrical functions.