Are "étalé spaces" a thing for probability spaces? Let $PX$ be a $\sigma$-algebra on the set $X$, and let $j : PX \to {\sf Set}_{/X}$ be the tautological functor that sends an event $E\subseteq X$ to itself, regarded as a function with codomain $X$. Now, the category ${\sf Set}_{/X}$ is cocomplete, thus $j$ has a unique cocontinuous extension to a pair of functors
$$
J : [PX^\text{op},{\sf Set}] \leftrightarrows {\sf Set}_{/X} : N
$$ here, $J$ is the left Kan extension of $j$ along the Yoneda embedding and $N$ is its right adjoint.
Most of you will have noticed that I am copying the exact procedure that yields the equivalence between sheaves on a topological space $X$ (so, a subcategory of $[OX^\text{op},{\sf Set}]$: open subsets instead of events, but the idea is the same) and étalé spaces (those $(E,p : E\to X) \in {\sf Top}_{/X}$ that are local homeomorphisms).

I am interested in the properties of the adjunction $(J,N)$.

I have no exact request apart a little bit of help wrapping my head around this construction, with particular attention to what is different from the topological case.
For the moment, let me just add something about the functor $J = \text{Lan}_yj$: the Kan extension can be written acting on the presheaf $F$ as
$$
\int^{E\in PX} FE \otimes jE
$$
(here $\otimes$ is a tensor in ${\sf Set}_{/X}$, so –I think– a coproduct of as many copies of $E \hookrightarrow X$ as there are elements in $FE$; now you perform a suitable quotient on the coproduct of all these $FE \otimes jE$; the colimit is done in Set and then the universal property there yields a unique structure of object in Set/X for the colimit, because –if I remember well that a category is connected if and only if its twisted arrow category is– the colimit defining the coend is over a connected category).
I know this might appear a naive question, but I have always found this construction very specific to sheaf theory (to the point that the very name "sheaf" comes from a pictorial representation of how the functor $[OX^\text{op},{\sf Set}] \to {\sf Top}_{/X}$ acts: all in all, the colimit breaks into the coproduct $\coprod_{x\in X} \text{colim}_{U\ni x} FU$ of all the "stalks" of $F$, that are fibers "stemming" from the "root" $x$... The stalks are then tied together by a certain topology on the disjoint union). 

Is there a similar visual intuition for how $J$ acts on a... well, how would you call it? a pre—?

 A: Etale Spaces can be used to analyze Giry-algebras ($\mathcal{G}$-algebras), and hence (for a fixed object $X$) probability spaces on $X$ as follows.  First note that your functor $j$ above should read $j: \Sigma_X \rightarrow \mathbf{Meas}/X$,  which is analogous to the topological case (requiring continuous functions rather than just set functions).  Here, $\mathbf{Meas}$ is the category of separated measurable spaces - meaning $(2, Discrete)$ is a coseparator of the elements of $X$.  Now suppose $\pi_X:\mathcal{G}(X) \rightarrow X$ is a Giry-algebra. (The reason we require separated measurable spaces is that because if $X$ is not separated, then there are no $\mathcal{G}$-algebras  on $X$.) 
Now the slice category $\mathbf{Meas}/X$ is cocomplete, and one has the same construction as you have noted above,  which is just Thm. 2, pp41-42 of Sheaves in Geom. & Logic (SGL), so we have the cited adjunction between the left-Kan extension, $J$, and the functor $N$.     Now fix the object $\pi_X$ in $\mathbf{Meas}/X$, and using the adjunct pair $J \dashv N$, look at the universal arrow from $J$ to the object $\pi_X$, i.e., the counit of the adjunction at $\pi_X$.  $N(\pi_X)$ is the ''sections functor'', i.e.,  $N(\pi_X)(U) = \{s: U \rightarrow \mathcal{G}(X) \, | \, \pi_X \circ s = id_U\}$, and $J(N(\pi_X)) = \pi_X$. (Provided I haven't done something foolish, this is just applying the argument in equation 8-10 of the text SGL, p 42, taking $E=\pi_X$ and $P$=sections functor.)
OK. This is all ''standard fare'', and I haven't said anything that answers your question regarding how you interpret presheaves, etc. - and I've yet to work it out. Sheaf spaces are constructed with a horizontal and vertical slice interpretation. Toward that same end note the following two points:
(1) every $\mathcal{G}$-algebra, such as $\pi_X$, specifies a super convex space structure on the underlying set of $X$, via $\sum_{i=1}^{\infty} \alpha_i x := \pi_X( \sum_{i=1}^{\infty} \alpha_i \delta_{x_i})$. More specifically, there is a functor, $\mathbf{Meas}^{\mathcal{G}} \rightarrow \operatorname{\mathbb{R}_{\infty}-\mathbf{SCvx}}$, from $\mathcal{G}$-algebras to $\mathbb{R}_{\infty}$-coseparated super convex spaces. ($\mathbb{R}_{\infty}$ is the one-point extension of the real line $\mathbb{R}$ by a point ''$\infty$'', and that set has the obvious super convex space structure, i.e., $(1-r) u + r \infty = \infty$ for all $r \in (0,1]$.)  (The object $\mathbb{R}_{\infty}$ ''arises'' as follows.  Every convex space  is either a geometric convex space (meaning it embeds into a real vector space), a discrete convex space, or a mixture of the two (which is most common).  A geometric space is coseparated by the unit interval $[0,1]$.  A discrete space is coseparated by $\mathbf{2}$. In $\mathbf{SCvx}$ there is a map $\mathbf{2} \rightarrow \mathbb{R}_{\infty}$, taking $0 \mapsto \infty$ and $1 \mapsto 0$.  The space $\mathbb{R}_{\infty}$ can therefore coseparate any super convex space. (Borger & Kemp showed $\mathbb{R}_{\infty}$ is a coseparator for $\mathbf{Cvx}$, and by restricting to $\operatorname{\mathbb{R}_{\infty}-\mathbf{SCvx}}$ it is a coseparator for that category also.))
(2) The object $\pi_X: \mathcal{G}(X) \rightarrow X$ is a (weak) terminal object in $\mathbf{Meas}/X$ because if $f: Y \rightarrow X$ is an object in $\mathbf{Meas}/X$ then the composite $\eta_X \circ f: Y \rightarrow \mathcal{G}(X)$ is an arrow to $\pi_X$.  We know how the $\sigma$-algebra structure of $\mathcal{G}(X)$ is constructed - via the evaluation maps $ev_U: \mathcal{G}(X) \rightarrow \mathbb{R}$. 
Now to the main point.  The idea is that the fibers over $x \in X$, which are the ''vertical slices'', specify a (coseparated) super convex space, while the horizontal slices specify the measurable structure.   Taking $X = \mathbb{R}_{\infty}$, the $\mathcal{G}$-algebra is the expectation operator, 
$\mathbb{E}: \mathcal{G}(\mathbb{R}_{\infty}) \rightarrow \mathbb{R}_{\infty}$, sending $P \mapsto \int_{x \in \mathbb{R}_{\infty}} x dP$.  (Taking $P$ to be the half-Cauchy distribution, it is clear why we need $\infty$.)
Now suppose $X$ is an arbitrary (separated) measurable space with the $\mathcal{G}$-algebra $\pi_X$.  Then a commutative square, corresponding to a $\mathcal{G}$-algebra morphism $\hat{f}: \pi_X \rightarrow \mathbb{E}$ is specified by a measurable function $f: X \rightarrow \mathbb{R}_{\infty}$, which under the induced super convex space structures on $X$ and $\mathbb{R}_{\infty}$ is also a countably affine map (which is easy enough to verify directly).
This gives the basic idea of how you interpret (part of) the construction you are referring to.
Let me add some context.  Your coend formulation is correct - but you can view it from a slightly different point of view.   (The coend formulation is Prob. 5 on Page 223, CWM, MacLane.)  Let me use MacLanes's notation.  Let S be any presheaf, $S: \Sigma_X^{op} \rightarrow \mathbf{Set}$, and take $T: \Sigma_X \rightarrow \operatorname{\mathbb{R}_{\infty}-\mathbf{SCvx}}/\mathcal{G}(X)$ to be given by  $U \mapsto (\mathcal{G}(U) \hookrightarrow \mathcal{G}(X))$. Since $\operatorname{\mathbb{R}_{\infty}-\mathbf{SCvx}}$ is cocomplete, so is the slice over $\mathcal{G}(X)$, and an element of that slice category is any  ''kernel map'' $k: A \rightarrow \mathcal{G}(X)$. Then the tensor product of $S$ and $T$, which is the coend, is valued in $\operatorname{ \mathbb{R}_{\infty}-\mathbf{SCvx}} / \mathcal{G}(X)$, i.e., the tensor product is a kernel map.  (Note that the functor $T$ is just the composite of $j$ and the functor $\hat{\mathcal{P}}: \mathbf{Meas}/X \rightarrow \operatorname{ \mathbb{R}_{\infty}-\mathbf{SCvx}} / \mathcal{G}(X)$ which is induced by the functor $\mathcal{P}: \mathbf{Meas} \rightarrow \operatorname{ \mathbb{R}_{\infty}-\mathbf{SCvx}}$ which is just the Giry monad viewed as a functor into the category of super convex spaces.
